Address Format Problem

[KenCJohnston]

Hey,
I've got a list of a couple thousand names and addresses.
Unfortunately somewhere along the fields "Address Number" and "Street
Name" where merged together. Whoever merged them forgot to add the " "
Character inbetween. Now I have a bunch of addresses like "1234Main
St.","1111Grand" and "250E 10th St.". Any idea how I can fix these? I
figure I need to use a macro because I've been playing around with the
text string functions and can't find any thing that will work.

KCJ

 

[Guest]

Try this small user defined function:

Function blank_in(r As Range) As String
blank_in = ""
s = r.Value
n = Len(s)
trip = False
For i = 1 To n
sbit = Mid(s, i, 1)
If IsNumeric(sbit) Then
blank_in = blank_in & sbit
Else
If trip = False Then
trip = True
blank_in = blank_in & " " & sbit
Else
blank_in = blank_in & sbit
End If
End If
Next
End Function


if A1 contains:
123main street
then
=blank_in(A1)
will return
123 main street

 

[Guest]

You could copy the column over into a helper column, giving two columns with
the same data.........then, using ASAP Utilities, a free add-in available at
www.asap-utilities.com, there is a feature therein that will delete all
alpha-characters from one column, and another that will delete all numerical
characters from the other.....

Vaya con Dios,
Chuck, CABGx3

 

[Bill Ridgeway]

Lumping all elements of a name and / or address into one field is a common
problem by people who just don't realise the practical implications. It
would be a good idea to put 'house number' and 'Street' into separate
fields. This facilitates, amongst other things, searching and sorting on
those elements. Fore more information please go to
www.1001solutions.co.uk/Designing_a_spreadsheet.pdf

Regards.

Bill Ridgeway
Computer Solutions