split column into two columns and keep leading zeros

I need to split a column into two columns and retain leading zeros. The data would look like this:

For example, the original cell "ha0902.3" splits so that the first cell is "ha" and the second cell should be "0902.3" but no matter what I do I get "902.3" in the second cell. Also I don't want the result to be a formula but the actual data only. What I really want is to force the format to be text and display all characters but it always seems to treat it as a number.

I have tried the "Text to Columns" button on the Data ribbon and formatted each column to text and used special formatting but nothing has worked.

I have also tried:

Please Login or Register  to view this content.

to get the second cell, which works except that I end up with a formula in the cell and it displays the apostrophe at the beginning - '0902.3

Because I have a huge amount of data to convert (by my own standard), I would really like to use "Text to Columns" since it is quick and clean.

Thanks,
BuzzMicron

Hi,

If it sure that before the number if it is always two letters, then you can go for the text to column feature itself, but you need to go for "Fixed Width" option where you have the option where you want to split the text. or you can use the below formula as well

=--MID(D3,3,LEN(D3)-2)

Where the D3 is the cell which contains the text.

Regards,
Hari

Hari,

I thought I posted this yesterday, but I guess not.

Thank you.

Your formula works perfectly, although it actually appears that the Len function is not required because it seems to not sensitive to the string length variations. It gave me a column of formulas which I was then able to copy the column and paste with "Values and Source Formatting" and I had just the values formatted exactly as I needed them. Thanks again.

Regarding the "Text to Columns" button, I was not clear. Sorry. All the data is two letters, four digits, a decimal point and then from one to three digits or letters. I was using "Text to Columns" with the "Fixed Width" option but it always drops the leading zero when there is one.

My sense of directness and simplicity wants "Text to Columns" to work and it also gives me the data and not a formula, which is good, but I will use the MID function and get it done, but if someone knows how to make this other method work, then I would like to know how, but it is not critical at this point. Thank you.

Help.jpg

It can be done like "martindwilson" suggested.