Splitting the address field: Edie's Question

[Aladin Akyurek]

Edie provided the following sample at

http://www.mrexcel.com/wwwboard/messages/23005.html

{"10 Westchester Ct";
"102 Crab Tree Ln";
"1002 N 50 E";
"106 N Couty Rd 50 W";
"1066 S Baums Bridge Rd";
"1068 N 100 E"}

which must be split into 2 columns, one holding the initial number, the other the rest of the address.

Activate Tools|Macro|Visual Basic Editor.
Activate Insert|Module.
Paste the UDF that follows in the open space of the window, entitled ...(Code).

Option Explicit

Function REVERSETEXT(text) As String
'
' Returns its argument, reversed
' J. Walkenbach
'
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
REVERSETEXT = REVERSETEXT & Mid(text, i, 1)
Next i
End Function

Activate File|Close and Return to Microsoft Excel.

Assuming that the first address entry is in A1,

in B1 enter: =LEFT(A1,SEARCH(" ",reversetext(A1)))+0

in C1 enter: =TRIM(REPLACE(A1,1,LEN(B1),""))

Select B1:C1 and give a double click on the little black square on the lower right corner of C1 in order to copy the formulas.