moving part of a column into another column

  • Thread starter Thread starter notsogoodatexcel
  • Start date Start date
N

notsogoodatexcel

I have in a columnin excel and address which i need to move the words which
are written in caps to the next column. eg 65 John Street NEWTOWN VIC 3505 -
I need to separate the NEWTOWN VIC 3505 to another column. there are 3500
rows in the spreadsheet. Can this be done?
 
Please find the below macro to split this. Col A (1 to n) will have your
data. The macro will split the contents to ColB and ColC. Please try and
feedback

Sub SplitUPPERStringfromEnd()
Dim intRow, intTemp, strData
intRow = 1

Do While Range("A" & intRow) <> ""
strData = StrReverse(Trim(Range("A" & intRow)))
For intTemp = 1 To Len(strData)
If Asc(Mid(strData, intTemp, 1)) > 96 And Asc(Mid(strData, intTemp, 1))
< 123 Then
Range("B" & intRow) = Trim(StrReverse(Mid(strData, intTemp)))
Range("C" & intRow) = Trim(StrReverse(Left(strData, intTemp - 1)))
Exit For
End If
Next
intRow = intRow + 1
Loop

End Sub


If this post helps click Yes
 
Sorry, if you are new to macros

Set the Security level to low/medium in (Tools|Macro|Security). Launch VBE
using short-key Alt+F11. Insert a module and paste the code. Get back to
Workbook. Tools|Macro| will list all available macros. Select and Run the
macro..

If this post helps click Yes
 
Just better hope there are no addresses in the OP's data that look like this
street address...

http://www.mapquest.com/maps/map.ad...on&state=DC&zipcode=20008-3021&searchtab=home

The following code will handle that problem, but could fail if there is a
town named, say, MT. VERNON... so the code for the OP to use depends on his
knowledge of the structure of his data.

Sub SplitUPPERStringfromEnd()
Dim X As Long, intRow As Long, R As Range
intRow = 1
Do While Range("A" & intRow) <> ""
Set R = Range("A" & intRow)
For X = 1 To Len(R.Value)
If Mid(R.Value, X, 4) Like " [A-Z][A-Z][A-Z]*" Then
R.Offset(, 1).Value = Left(R.Value, X - 1)
R.Offset(, 2).Value = Mid(R.Value, X + 1)
Exit For
End If
Next
intRow = intRow + 1
Loop
End Sub
 
Back
Top