Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
Jen wrote:
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 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 )) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jen" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
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 On Thu, 15 Oct 2009 09:17:17 -0700, Jen wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
If your first statement is true, then in B1:
=LEFT(A1,LEN(A1)-4) Gord Dibben wrote: 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 On Thu, 15 Oct 2009 09:17:17 -0700, Jen wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
Good catch.
I tend to make things more convoluted than necessary. Gord On Thu, 15 Oct 2009 14:54:46 -0500, Glenn wrote: If your first statement is true, then in B1: =LEFT(A1,LEN(A1)-4) Gord Dibben wrote: 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 On Thu, 15 Oct 2009 09:17:17 -0700, Jen wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to 'split' data from 1 column into 2??
On Thu, 15 Oct 2009 09:17:17 -0700, Jen wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut to split data from one column into three? HELP! | Excel Worksheet Functions | |||
Split Data in One Column Into Two | Excel Discussion (Misc queries) | |||
Split data in column | Excel Worksheet Functions | |||
How do I split data in a column (formula)? | Excel Discussion (Misc queries) | |||
How to split one column into two? | Excel Worksheet Functions |