PDF Print E-mail
How to split numbers and text from a string in Excel

 

I received an e-mail from Marc, in which he asked me whether it is posible to split numbers and text in Excel.

The answer of the solution is based upon determining the first position of a number in a string, you can do that by using the following formula.
=MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))

 

The next trick is to determine the text within a string:
A101 will become A when using the following formula =LEFT(A1;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))-1))
101A will become A when using the following formula =RIGHT(A2;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))))

 

To determine the numbers within a string:
A101 will become 101 when using the following formula =RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1)
101A will become 101 when using the following formula =LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))

 

You might want to convert the text string to numbers by using the following formula.
=VALUE()
A101 will become numeric value 101 when using the following formula =VALUE(RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1))
101A will become numeric value 101 when using the following formula =VALUE(LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))))

 

Suggestions for improving this article are welcome, please let me know and This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

 

Sponsored Links