Splitting string into letters and numbers

[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

 

[Jason Morin]

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

 

[Guest]

Thanks Jason, that was a big help!

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