PDA

View Full Version : [SOLVED] Break at First Number in String



Anne Troy
04-18-2005, 08:45 AM
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
04-18-2005, 09:23 AM
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
04-18-2005, 09:39 AM
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
04-18-2005, 09:51 AM
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
04-18-2005, 10:17 AM
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
04-18-2005, 10:23 AM
egads that's ugly.
LOL!!

But I like your thinking, Tony. :)

Zack Barresse
04-18-2005, 10:25 AM
egads that's ugly.
LOL!!

But I like your thinking, Tony. :)
But much more elegant and quicker than a looped macro. :yes

Jacob Hilderbrand
04-18-2005, 10:34 AM
Anne, which line errored for you? Can you post the workbook or email it to me?

Anne Troy
04-18-2005, 11:14 AM
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
04-18-2005, 11:45 AM
Ok, I see. Try this.


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

Niko
04-19-2005, 01:56 AM
:hi: 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
04-19-2005, 02:16 AM
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. :)