Splitting string into letters and numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 there:

=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
 
Thanks Jason, that was a big help!

Jason Morin said:
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 there:

=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
Click to expand...
 
Back
Top