Break at First Number in String

[Anne Troy]

I want to break up a cell into two cells (like text-to-columns), using the space just before the first number in the cell as the delimiter.

So:

John B. Smith 34 South 3rd Street Quakertown 18951

Turns into:

Cell 1: John B. Smith
Cell 2: 34 South 3rd Street Quakertown 18951

[sandam]

This should do the trick for a single cell. A column might take me a little longer due to my lacking knowledge of excel Or one of ther true excel experts on this forum, very little time at all.

Sub SplitStringAtFirstNumber()
Dim LoopInt As Integer
Dim byteArray() As Byte
Dim Flag As Boolean
  'converting cell text into ascii values
  byteArray = StrConv(Range("A1").Value, vbFromUnicode)
  'Arrays created with StrConv are by default 0 based
  'so LoopInt is set to 0
  LoopInt = 0
  'set the flags so that we know where the first number encountered is
  Flag = True
  While Flag = True
    'when you encounter the first number, end check with flag
    If byteArray(LoopInt) > 47 And byteArray(LoopInt) < 58 Then
      Flag = False
    End If
    LoopInt = LoopInt + 1
  Wend
  'this loop adds one too many so we subtract one to get the right value
  'for the left and right functions
  LoopInt = LoopInt - 1
  'Take first string and paste it into the ajoining cell
  'Using left with looping as the marker of the space in the string
  Range("B1").Value = Left(Range("A1").Value, LoopInt)
  'First change loopint around so that it will take the rest of the
  'text in the cell after the space
  LoopInt = Len(Range("A1").Value) - LoopInt
  'Now take the second string and paste it into the next cell
  'using the right function
  Range("C1").Value = Right(Range("A1").Value, LoopInt)
  'Which still leaves the original value in the first cell
End Sub

[Jacob Hilderbrand]

This will work for the whole column.

Option Explicit

Sub Macro1()
Dim i               As Long
Dim j               As Long
Dim k               As Long
Dim n               As Long
Dim LastRow         As Long
    LastRow = Range("A65536").End(xlUp).Row
    For i = 1 To LastRow
        k = 0
        n = 0
        For j = 0 To 9
            k = InStr(1, Range("A" & i).Text, j)
            If k > 0 Then
                If n = 0 Then
                    n = k
                Else
                    n = Application.WorksheetFunction.Min(n, k)
                End If
            End If
        Next j
        If n > 0 Then
            Range("B" & i).Value = _
            Mid(Range("A" & i).Text, n, Len(Range("A" & i).Text))
Range("A" & i).Value = _
            Left(Range("A" & i).Text, n - 2)
        End If
    Next i
End Sub

[Anne Troy]

That's beautiful, Jake.

I got an error on the very last range line, but it still performed beautifully, as though the last range line thang isn't needed.

[TonyJollans]

And here's a way to do it without code.

If your original data is in A2, then

In B2 array-enter (Ctrl+Shift+Enter)

=MIN(IF(ISERR(FIND({0,1,2,3,4,5,6,7,8,9},A2)),LEN(A2)+1,FIND({0,1,2,3,4,5,6,7,8,9},A2)))

In C2 enter

=LEFT(A2,B2-1)

In D2 enter

=MID(A2,B2,LEN(A2))

Copy down as far as your data and, if you like, Copy and Paste Special > Values columns C and D to themselves and delete column B.

[Anne Troy]

egads that's ugly.
LOL!!

But I like your thinking, Tony.

[Zack Barresse]

egads that's ugly.
LOL!!

But I like your thinking, Tony.

But much more elegant and quicker than a looped macro.

[Jacob Hilderbrand]

Anne, which line errored for you? Can you post the workbook or email it to me?

[Anne Troy]

This one:

Range("A" & i).Value = _ 
            Left(Range("A" & i).Text, n - 2)

But don't worry about it; it works. I'll send you the workbook later if you need it.

and I never got notifs of yours or Zack's post

k.
It's erroring when there's a zero as the first letter (which can happen 'cause we scanned the docs). They should be O's.

[Jacob Hilderbrand]

Ok, I see. Try this.

k = InStr(2, Range("A" & i).Text, j)

[Niko]

Hi Dreamboat,

Just check out the following link:

http://www.cpearson.com/excel/FirstLast.htm

This may be of ur help.

Cheers
Niko

[Anne Troy]

Actually, it's over 3000 records, and Jake's (drj) code works absolutely perfect. It fails if the first character in the cell is a number, but that's GOOD.

These addresses were scanned. I'm not trying to separate names, but addresses from names.

Marking it solved.