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

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
.

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
.