Should Be Easy!

I have a question which should be an easy one for most of the pros on this site. I need to separate data in one collumn of access into two collumns. The separating I have a collumn with data similar to the following

756398__98076

I need to separate this into two Collumns . . .
756398 98076
The collumn does not contain a consistent amount of characters before or after the "__" separator so i can't use any of the built in functions. I can do this easily in EXCEL using the "text to column" function but don't know how to do this in Access.

Could you just do it in excel and then import table to Access? That might be the quickest. Or do you need it to be all auto.

Your formula would be:

This will get the left side of the column

Left(FieldName, InStr(1, FieldName, "_") - 1)

For the right side use the following

Right(FieldName, Len(FieldName) - InStr(1, FieldName, "_"))

lwells

I generally work with datasets containing 100,000 or more rows. To my knowledge Excell can't accept over 65000 or so.

Thanks a ton. I still have a problem though. The second formula (Right(FieldName, Len(FieldName) - InStr(1, FieldName, "_")) leaves a "_" before all values in the column. I suspect this is due to the fact that there are two underscores in the initial field. Can you offer any suggestions? See Example

Initial Field First Formula Second Formula
014544-300-01__00010450 014544-300-01 _00010450

Just subtract one of the underscores like below

Right(FieldName, Len(FieldName) - InStr(1, FieldName, "_") -1)

lwells

I can't thank you enough!