Text to Columns - Delimited with a really weird issue with scientific notation after running

[jlax2485]

Hi,

I am running into a strange problem and I haven't been able to find a suitable solution to it. I have a macro which I am running a text-to-columns on a column filled names and ID's sandwiched together like this:

465E10^JONES, JOHN.

The text to columns work fine with the consistent ^ delimiter, but the problem with this specific instance, is that once the above string is split, excel treats it as scientific notation, and the cell value immediately turns into the number 4650000000000.

I have tried different things to work around this...basically everything in my application depends on this ID number, and if it does not format correctly then my subs will not work.

Is there anything I could do to work around this issue in VBA? This is the first instance of this happening out of hundreds of ID's, but I am sure it will come up again.

Thanks for your help.

Josh

 

[Fluff]

On step 3 of the text to columns wizard, select the column you're interested in & then select text, rather than general

 

[Rick Rothstein]

I am running into a strange problem and I haven't been able to find a suitable solution to it. I have a macro which I am running a text-to-columns on a column filled names and ID's sandwiched together like this:

465E10^JONES, JOHN.

The text to columns work fine with the consistent ^ delimiter, but the problem with this specific instance, is that once the above string is split, excel treats it as scientific notation, and the cell value immediately turns into the number 4650000000000.

Try adding this to the end of your TextToColumns method call in your macro...

FieldInfo:=Array(Array(1, 2), Array(2, 2))

 

[jlax2485]

Try adding this to the end of your TextToColumns method call in your macro...

FieldInfo:=Array(Array(1, 2), Array(2, 2))

Excellent- worked perfectly. Thanks so much for the prompt reply!