Splitting text

[LLG-CN]

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?

 

[Mike H]

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&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Then this in c1 to extract the name
=RIGHT(A1,LEN(A1)-LEN(B1))

Drag down as required

Mike

 

[JLatham]

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.

 

[JLatham]

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

 

[Shane Devenshire]

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

 

[JLatham]

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]

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]

Thanks, this worked just like I needed it to and was very simple.

 

[Mike H]

Glad I could help with a 'simple' formula!

Mike

Thanks, this worked just like I needed it to and was very simple.

 

[LLG-CN]

Well, yes, simple is relative. Maybe short was a better term.
I would probably have never figured this one out.

 

[Ron Rosenfeld]

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

 

[JLatham]

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.