I need help to split a field that currently contains a business name and an address. Here is a few examples listed below. The split should be the occurrence of the first numeral. Thank you!


SHAMPOOCHES 3124 CROW CANYON PL A
TAIL WAGGERS PET & HOME CARE 3101 LAKEMONT DR 3
RAD REPUBLIC THREADS 5355 CYPRESS HAWK CT
FREDDIE MAC BRAND 111 DEERWOOD RD 200
MEN'S WEARHOUSE, THE #2168 2441 SAN RAMON VALLEY BLV

Hi,

Am Sat, 24 May 2014 23:49:53 +0100 schrieb Simontan:

SHAMPOOCHES 3124 CROW CANYON PL A
TAIL WAGGERS PET & HOME CARE 3101 LAKEMONT DR 3
RAD REPUBLIC THREADS 5355 CYPRESS HAWK CT
FREDDIE MAC BRAND 111 DEERWOOD RD 200
MEN'S WEARHOUSE, THE #2168 2441 SAN RAMON VALLEY BLV

with a look at your examples I found out that you have two spaces in
front of the address.
If your examples are in column A then try in B1:
=LEFT(A1,FIND(" ",A1)-1)
and in C1:
=MID(A1,FIND(" ",A1)+1,99)

If you get wrong results because there are not always two spaces try it
with this macro:

Sub SplitAddress()
Dim i As Long, j As Long, n As Long
Dim rngC As Range
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
For n = 0 To 9
i = InStrRev(rngC, n, Len(rngC) - 5)
If i < 0 Then Exit For
Next
j = InStrRev(rngC, " ", i)
Cells(rngC.Row, 2) = Left(rngC, j - 1)
Cells(rngC.Row, 3) = Mid(rngC, j + 1, 99)
Next

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Sat, 24 May 2014 23:49:53 +0100, Simontan wrote:


I need help to split a field that currently contains a business name and
an address. Here is a few examples listed below. The split should be the
occurrence of the first numeral. Thank you!


SHAMPOOCHES 3124 CROW CANYON PL A
TAIL WAGGERS PET & HOME CARE 3101 LAKEMONT DR 3
RAD REPUBLIC THREADS 5355 CYPRESS HAWK CT
FREDDIE MAC BRAND 111 DEERWOOD RD 200
MEN'S WEARHOUSE, THE #2168 2441 SAN RAMON VALLEY BLV

The position of the first numeral can be found using the formula:

MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

Hence, the business name:

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))-1))

and the address:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),99)

(the '99' is merely sum number sure to be larger than the longest address)