| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Splitting string into letters and numbers
Hey there all,
I've got a few strings that need sorting. Some examples could be: ABC123 ABFYE6 BHYF1478 I need to separate the letters and the numbers into two different columns. How can this be done? Is there a way to identify whether a character is a number, letter or symbol or something? Thanks very much, Pete -- Cheers, Foss |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Splitting string into letters and numbers
With a formula you can identify the position of the 1st
number with: =MIN(IF(ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN (A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))) Array-entered. From there you can use MID, LEFT, or RIGHT to split it, or even insert a delimiter like "|" and split it from the =REPLACE(A1,MIN(IF(ISNUMBER(1*MID(A1,ROW(INDIRECT( "1:"&LEN (A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),,"|") Also array-entered. HTH Jason Atlanta, GA -----Original Message----- Hey there all, I've got a few strings that need sorting. Some examples could be: ABC123 ABFYE6 BHYF1478 I need to separate the letters and the numbers into two different columns. How can this be done? Is there a way to identify whether a character is a number, letter or symbol or something? Thanks very much, Pete -- Cheers, Foss . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Splitting string into letters and numbers
Thanks Jason, that was a big help!
"Jason Morin" wrote: With a formula you can identify the position of the 1st number with: =MIN(IF(ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN (A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))) Array-entered. From there you can use MID, LEFT, or RIGHT to split it, or even insert a delimiter like "|" and split it from the =REPLACE(A1,MIN(IF(ISNUMBER(1*MID(A1,ROW(INDIRECT( "1:"&LEN (A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))),,"|") Also array-entered. HTH Jason Atlanta, GA -----Original Message----- Hey there all, I've got a few strings that need sorting. Some examples could be: ABC123 ABFYE6 BHYF1478 I need to separate the letters and the numbers into two different columns. How can this be done? Is there a way to identify whether a character is a number, letter or symbol or something? Thanks very much, Pete -- Cheers, Foss . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for detect palindromes and repeats in letters/numbers string | Excel Worksheet Functions | |||
| Macro for detect palindromes and repeats in letters/numbers string | New Users to Excel | |||
| Splitting complex string into 3 numbers with a formula - Please help! | Excel Worksheet Functions | |||
| How can I convert a character string that contains letters & numbers "£2,456.99 (including flights)" into a numeric? | Excel Worksheet Functions | |||
| Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
![Foss[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
