[Solved] Extracting text from an entry

[edinflorida]

I entered a series of numbers in a new spreadsheet. I changed nothig re. formats.

so the entries look like
1000
1001
1002
1003a
1004a
1005b

etc.

Thing is that I need to be able to sort the number part so I created a series of new columns
Text(a1,0)
Left(a1;Len(a1)-1)) <--- Gives me the number part
Right(a1,1) <--- Gives me the letter part

Here is what I get:

149a      149a      149      a
1519a     1519a     1519     a
1539a     1539a     1539     a
1558a     1558a     1558     a
1567a     1567a     1567     a
1568a     1568a     1568     a
1624a     1624a     1624     a
1685e     1685      168      5
1686e     1686      168      6
1688e     1688      168      8
1690e     1690      169      0
1692e     1692      169      2

Sometimes it works, sometimes it doesn't.
I'll need to convert the number part back to a number. That info would be good too. Meanwhile is there a rational explanation for this result or is it simply further evidence for the existence of the paranormal?

BTW, it happens for all letters and string lengths.

Thank you.

[FJCC]

In the examples you give, I suspect the "e" is being interpreted as a marker for a power of 10. I suggest you drop the use of the TEXT() function. I don't think it is necessary. You could also format column A as text. If you still have trouble, please upload a file with examples. Click Post Reply and look for the Attachments tab just below the box where you type a response.

[edinflorida]

I ended up just doing it by hand. Thanks anyway.