I received an e-mail from Marc, in which he asked me whether it is possible to split numbers and text in Excel. The answer to 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 drop me a line.