Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving part of a column into another column
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving part of a column into another column
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 --------------- Jacob Skaria |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving part of a column into another column
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 --------------- Jacob Skaria |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving part of a column into another column
Just better hope there are no addresses in the OP's data that look like this
street address... http://www.mapquest.com/maps/map.adp...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 -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ron DeBruin Macro - Moving Sheet Name from Last Column to Column A | Excel Worksheet Functions | |||
Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
Copying Part of a row down part of a column | Excel Discussion (Misc queries) | |||
What to do to fill column 2 with part of whats in column 1 | Excel Discussion (Misc queries) | |||
Why is my tab key moving my cursor from column A to column k? | Excel Discussion (Misc queries) |