Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
I have a spreadsheet that contains a column that has numbers and text of
varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Hi,
With your string in a1 put this in B1 to extract the numbers =LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) Then this in c1 to extract the name =RIGHT(A1,LEN(A1)-LEN(B1)) Drag down as required Mike "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Someone may come along with a formula to replace my use of a User Defined
Function (UDF), which would be a better deal for you, but here's my solution. First I created a UDF that finds the digits at the left side of the entry (assumes your data is all like your examples). That UDF is put into a cell. Then a regular cell formula is put into another cell to get whatever is left from the original and display it. You'll understand better when you see it. First, the UDF. Use [Alt]+[F11] to enter the VB Editor and choose Insert | Module when you get there. Copy the code below and paste it into the empty module presented to you. Close the VB Editor. Lets say that 123John Doe is in cell A1, then in B1 put this formula: =GetDigits(A1) and in C1 put this formula =RIGHT(A1,LEN(A1)-LEN(B1)) You should see 123 in B1 and John Doe in C1. Hope this helps. "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
OOPS!! Here's the UDF you need:
Function GetDigits(whatCell As Range) As String Dim srcString As String Dim LC As Integer GetDigits = "" srcString = whatCell.Value For LC = 1 To Len(srcString) If Mid(srcString, LC, 1) = 0 And Mid(srcString, LC, 1) <= "9" Then GetDigits = GetDigits & Mid(srcString, LC, 1) Else 'must have hit non-digit, quit Exit Function End If Next End Function "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Hi,
Try =LEFT(A1,MATCH(TRUE,ISERR(--MID(A1,ROW($1:$1000),1)),0)-1) =MID(A1,MATCH(TRUE,ISERR(--MID(A1,ROW($1:$1000),1)),0),100) Both array entered - press Shift+Ctrl+Enter to enter them If this helps, please click the Yes button, Cheers, Shane Devenshire "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
And for my FINAL faux-pas of the day, a version of the UDF that should work
much better, and more reliably than the other (which actually works, but wasn't that well written). Function GetDigits(whatCell As Range) As String Dim srcString As String Dim LC As Integer GetDigits = "" srcString = whatCell.Value For LC = 1 To Len(srcString) If Mid(srcString, LC, 1) = "0" And Mid(srcString, LC, 1) <= "9" Then GetDigits = GetDigits & Mid(srcString, LC, 1) Else 'must have hit non-digit, quit Exit Function End If Next End Function "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Thanks for all the help.
The formula Mike H submitted worked like a charm and was very simple and straight forward. Thanks so much! "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Thanks, this worked just like I needed it to and was very simple.
"Mike H" wrote: Hi, With your string in a1 put this in B1 to extract the numbers =LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) Then this in c1 to extract the name =RIGHT(A1,LEN(A1)-LEN(B1)) Drag down as required Mike "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Glad I could help with a 'simple' formula!
Mike "LLG-CN" wrote: Thanks, this worked just like I needed it to and was very simple. "Mike H" wrote: Hi, With your string in a1 put this in B1 to extract the numbers =LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) Then this in c1 to extract the name =RIGHT(A1,LEN(A1)-LEN(B1)) Drag down as required Mike "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
Well, yes, simple is relative. Maybe short was a better term.
I would probably have never figured this one out. "Mike H" wrote: Hi, With your string in a1 put this in B1 to extract the numbers =LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) Then this in c1 to extract the name =RIGHT(A1,LEN(A1)-LEN(B1)) Drag down as required Mike "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
On Wed, 12 Nov 2008 08:10:00 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote: Function GetDigits(whatCell As Range) As String Dim srcString As String Dim LC As Integer GetDigits = "" srcString = whatCell.Value For LC = 1 To Len(srcString) If Mid(srcString, LC, 1) = "0" And Mid(srcString, LC, 1) <= "9" Then GetDigits = GetDigits & Mid(srcString, LC, 1) Else 'must have hit non-digit, quit Exit Function End If Next End Function Since the will always be at the beginning of the string, in this instance you could use the Val function: ============================= Option Explicit Sub GetDigits() Dim rg As Range, c As Range Set rg = Selection For Each c In rg c.Offset(0, 1).Value = Val(Trim(c.Value)) Next c End Sub ======================== --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting text
See, I was right - someone came along (probably typing at the same time I
was) with a formula vs the UDF, and that's generally less cumbersome to work with. "LLG-CN" wrote: Thanks for all the help. The formula Mike H submitted worked like a charm and was very simple and straight forward. Thanks so much! "LLG-CN" wrote: I have a spreadsheet that contains a column that has numbers and text of varying lengths that I need to split into 2 columns. Example: 123John Doe 1234Jane Doe I can't split it using the Text To Columns function because there's nothing delimited the text and the fixed width doesn't work because the numbers preceeding the text are varying lengths. Does anyone know a way to extract the data? Is there something that extracts numerals vs characters? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Splitting Text | New Users to Excel | |||
Splitting a delimited text | Excel Discussion (Misc queries) | |||
Splitting text to columns | Excel Worksheet Functions | |||
splitting text in a cell.. | Excel Discussion (Misc queries) | |||
Splitting Up Text in One Cell To Many | Excel Worksheet Functions |