Data seperated by a ;

[T Miller]

I have a table and in the table there is a column with data in the column
seperated by a ; I know I can use text to columns in excel but this is
670,000 rows. Is there a way to seperate this in the table?

 

[T Miller]

Thanks Karl,

I will try this.
--
Thomas

The best thing is to add a new field so as to permamently separate the data.
Backup the database.
Use this to update the new field with the information to the right of the
semicolon ---
NewFieldData: Right([OldField], Len([OldField])-Instr([OldField],";"))

Then update the old field using this --
OldFieldUpdate: Left([OldField], Instr([OldField],";")-1)

I have a table and in the table there is a column with data in the column
seperated by a ; I know I can use text to columns in excel but this is
670,000 rows. Is there a way to seperate this in the table?

 

[T Miller]

Jerry,

I only wish I had Excel 07' at work esp. if it now holds more rows, then
most of my problems would be over. As for the ; yeah there is only one in
the column, I have thought about doing this as well. If the above does not
work I am going to have to go this route, it is just a long one to go on.

 

[Jerry Whittle]

I think that Excel 2007 will handle that many rows now!

Is there a set number of semi-colons in that field like always one or two?
If so you could export the data to a text file. Then you could import the
text file into another table and use the ; as the delimiter. Just be very
sure that the present table has a primary key defined and if you choose to
just export the problem column to also include the primary key field.

 

[fredg]

I have a table and in the table there is a column with data in the column
seperated by a ; I know I can use text to columns in excel but this is
670,000 rows. Is there a way to seperate this in the table?

Do you mean you wish to permanently add a new field to the table?

*** Back up your table data first. ***

Add the new field, Text datatype to your table.

Then run an Update query.

Update YourTable Set YourTable.NewField =
Mid([OldField],InStr([OldField],";")+1),YourTable.OldField =
Left([OldField],InStr([OldField],";")-1)

 

[KARL DEWEY]

The best thing is to add a new field so as to permamently separate the data.
Backup the database.
Use this to update the new field with the information to the right of the
semicolon ---
NewFieldData: Right([OldField], Len([OldField])-Instr([OldField],";"))

Then update the old field using this --
OldFieldUpdate: Left([OldField], Instr([OldField],";")-1)