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.

**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.