2-line address in one cell

[cindee]

Hey - I promised a friend I'd help get some addresses
into a spreadsheet. There's 25,000 of them! My problem
is that the street address for each one looks like this:

123 West High Ridge Street
San Francisco, Ca 98765

All in one cell. I was thinking of Text to Columns but
in a long street name like 123 West High Ridge Street,
I'd end up with 5 columns where I want 1.

Help! I'm in address hell!

cr

 

[Don Guillett]

See if this helps
Sub findchr10()
For Each c In Selection
c.WrapText = False
x = InStr(c, Chr(10))
c.Offset(, 1) = Left(c, x)
c.Offset(, 1).Replace Chr(10), ""
c.Offset(, 1).WrapText = False
c.Offset(, 2) = Right(c, Len(c) - x)
Next
End Sub

 

[Gord Dibben]

Cindee

What happens when you turn off "wrap text" under Format>Cells>Alignment?

Do you see a square block between the two lines?

If so, this is probably a code 0010

Try Edit>Replace

what: using NumPad enter ALT + 0010(you won't see it, but it will be there)

with: space

Replace.

Gord Dibben Excel MVP

 

[Guest]

Thanks! That's handy - I used a unique character rather
than a space so I could use Text-to-Columns, though.
However, even though that worked to put address in a
different column, I've still got an issue with City,
State and ZIP being in one column. I'm not sure how to
break this into multiple columns since my city may be
more than one word such as Santa Fe. Should I just give
up and split everything into multiple columns and
concatenate by hand?

cr

 

[Gord Dibben]

I have obviously mis-understood your requirements.

"I'd end up with 5 columns where I want 1"

Do you want the street address in one column and the City, State and ZIP in 3
more columns?

You say you have the street address now in one column and city, state and ZIP
in another.....right?

If the city always has a comma after it, you could first split on delimited by
comma then split state and ZIP by space.

Gord Dibben Excel MVP

 

[Myrna Larson]

Here's some code I wrote some time ago to split a cell containing something
like

New York, New York 10021

into 3 cells. It uses the Split function that was introduced in XL2000, so you
need that version or higher. It handles states that are written out, e.g.
Illinois, and state names that are two words (New York, South Carolina, West
Virginia, etc), as above, or the two letter abbreviations like NY, SC, WV.

To use the function, you need to go to the VB Editor (ALT+F11), select your
workbook over in the project pane on the upper left, insert a module into your
project (VBE's Insert menu), then paste the code below (all lines *between*
the lines of tildes) into the new code pane you see on the right.

On the worksheet, let's say your City-State-Zip is in cell D2. Select 3 cells,
E2:G2, enter the formula

=CityStateZip(D2)

and press CTRL+SHIFT+ENTER to enter it as an array formula. The city will be
placed in E2, state in F2, and Zip in G2.

Copy the formula down, then to convert to values, select the three columns,
Edit/Copy them, then Edit/Paste Special and select the Values option.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Option Compare Text

Function CityStateZip(sAddress As String) As Variant
Dim sTemp As String
Dim Words() As String
Dim N As Long
Dim City As String
Dim State As String
Dim Zip As String
Dim TwoWords As Boolean

'remove leading and trailing spaces and reduce
'runs of embedded spaces to a single space
sText = Application.Trim(sText)

'split the into individual words at the spaces
Words = Split(sTemp, " ")

N = UBound(Words)

If N < 2 Then
'require that there are at least 3 words
'representing city, state, and zip code
Zip = "???"
State = "???"
City = sTemp

Else
Zip = Words(N)
State = Words(N - 1)
ReDim Preserve Words(0 To N - 2)

TwoWords = False
If Len(State) > 2 And N >= 3 Then
'state is written out

sTemp = UCase$(State)
Select Case UCase$(Words(N - 2))
Case "NEW"
Select Case sTemp
Case "YORK", "JERSEY", "HAMPSHIRE", "MEXICO"
TwoWords = True
End Select
Case "NORTH", "SOUTH"
Select Case sTemp
Case "CAROLINA", "DAKOTA"
TwoWords = True
End Select
Case "WEST"
TwoWords = (sTemp = "VIRGINIA")
Case "RHODE"
TwoWords = (sTemp = "ISLAND")
End Select

If TwoWords Then
State = Words(N - 2) & " " & State
ReDim Preserve Words(0 To N - 3)
End If
End If

'concatenate what's left in the array as the city
City = Join(Words, " ")
If Right$(City, 1) = "," Then City = Left$(City, Len(City) - 1)

End If 'at least 3 words

CityStateZip = Array(City, State, Zip)

End Function 'CityStateZip

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Myrna Larson]

PS: To do 25000 rows, the recalculation will take some time. Be patient!

 

[Myrna Larson]

Dump the previous version of this routine. I didn't test it before posting.

It won't compile. It looks like I did an incomplete job of changing variable
names. This version works correctly.

Option Explicit
Option Compare Text

Function CityStateZip(sAddress As String) As Variant
Dim sTemp As String
Dim Words() As String
Dim N As Long
Dim City As String
Dim State As String
Dim Zip As String
Dim TwoWords As Boolean

'remove leading and trailing spaces and reduce
'runs of embedded spaces to a single space
sTemp = Application.Trim(sAddress)

'split the into individual words at the spaces
Words = Split(sTemp, " ")

N = UBound(Words)

If N < 2 Then
'require that there are at least 3 words
'representing city, state, and zip code
Zip = "???"
State = "???"
City = sTemp

Else
Zip = Words(N)
State = Words(N - 1)
ReDim Preserve Words(0 To N - 2)

TwoWords = False
If Len(State) > 2 And N >= 3 Then
'state is written out

sTemp = UCase$(State)
Select Case UCase$(Words(N - 2))
Case "NEW"
Select Case sTemp
Case "YORK", "JERSEY", "HAMPSHIRE", "MEXICO"
TwoWords = True
End Select
Case "NORTH", "SOUTH"
Select Case sTemp
Case "CAROLINA", "DAKOTA"
TwoWords = True
End Select
Case "WEST"
TwoWords = (sTemp = "VIRGINIA")
Case "RHODE"
TwoWords = (sTemp = "ISLAND")
End Select

If TwoWords Then
State = Words(N - 2) & " " & State
ReDim Preserve Words(0 To N - 3)
End If
End If

'concatenate what's left in the array as the city
City = Join(Words, " ")
If Right$(City, 1) = "," Then City = Left$(City, Len(City) - 1)

End If 'at least 3 words

CityStateZip = Array(City, State, Zip)

End Function 'CityStateZip