How to 'split' data from 1 column into 2??

J

Jen

Hi!

Can anyone help me with a formula for Excel 2003 to split data from 1 column
into 2?

Data Examples:
COUN1234
PS3524
PSY5002

I want to have a formula to split the alpha data from the numeric data. As
you can see, the alpha data is of varying lengths.

Thanks ahead of time for anyone who can assist with this!

-Jen;-)

Jennifer Moulton
(e-mail address removed)
 
G

Glenn

Jen said:
Hi!

Can anyone help me with a formula for Excel 2003 to split data from 1 column
into 2?

Data Examples:
COUN1234
PS3524
PSY5002

I want to have a formula to split the alpha data from the numeric data. As
you can see, the alpha data is of varying lengths.

Thanks ahead of time for anyone who can assist with this!

-Jen;-)

Jennifer Moulton
(e-mail address removed)


The following two array formulas (commit with CTRL+SHIFT+ENTER) will work:

=LEFT(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)),0)-1)

=--MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1)),0),LEN(A1))
 
R

ryguy7272

You will nee to use a couple of CSE functions.

This will give you the text in each cell:
=IF(A1="","",LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

Enter with Ctrl+Shift+Enter, not just enter.

This will give you the numbers in each cell
=IF(A1="","",RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1))

Again, enter with Ctrl+Shift+Enter, not just enter,

HTH,
Ryan---
 
G

Gord Dibben

If you always have 4 trailing numbers.

In C1 enter =RIGHT(A1,4) copy down

In B1 enter =MID(A1,1,LEN(A1)-LEN(C1)) copy down


Gord Dibben MS Excel MVP
 
R

Ron Rosenfeld

Hi!

Can anyone help me with a formula for Excel 2003 to split data from 1 column
into 2?

Data Examples:
COUN1234
PS3524
PSY5002

I want to have a formula to split the alpha data from the numeric data. As
you can see, the alpha data is of varying lengths.

Thanks ahead of time for anyone who can assist with this!

-Jen;-)

Jennifer Moulton
(e-mail address removed)


The location of the first digit in your string is given by the formula:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9,0},A1&"0,1,2,3,4,5,6,7,8,9"))

Accordingly, to return the alpha data:

=LEFT(A1,-1+MIN(FIND({0,1,2,3,4,5,6,7,8,9,0},A1&"0,1,2,3,4,5,6,7,8,9")))

and to return the digits:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9,0},A1&"0,1,2,3,4,5,6,7,8,9")),99)

Note the 99 at the end. This just needs to be a value larger than the longest
set of digits in your data.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top