View Full Version : [SOLVED] How capture City and State values from one cell (with a delimeter character)

02-11-2015, 09:44 AM

I'm working on an excel macro to essentially switch the ordering of the city and state in a column, and also change the delimiter character.

Below, I have screenshots illustrating what I want the macro to do:

I have the majority of the Macro planned out, except for the hard part of actually identifying and storing the city and state values in the column B cells. In the below code, could someone enlighten me on how to do that properly in VBA? The part of interest is under the comment section that has a bunch of star (*) characters.

'Code for the SwitchOrder Excel Macro
Sub SwitchOrder()

'Specify which sheet we are working with in the workbook

Dim City As String
Dim State As String

City = ""
State = ""

'Select the first entry of the "B" column, which contains the city and state, separated by a comma.

'This following loop needs to swap the ordering of the city and state in the "B" column, and then the separator value needs to be a dash instead of a comma.
'I know the State value will always be 2 characters

'The Do Until condition signifies the end of data
Do Until Selection.Value = "" And Selection.Offset(0, -1).Value = ""

'Here, I need code to capture the City and State values in the selected cell
'************************************************************************** **************************
'/*Pseudocode for the following two lines of code - How do I do the below two lines properly in VBA?*/
'************************************************************************** **************************

City = EverythingToTheLeftOfTheCommaInSelectedCell
State = TheTwoCharactersToTheRightOfTheCommaMinusThePrecedingSpaceCharacterInSelect edCell

'Change the cell value to desired format
Selection.Value = State & " - " & City

'Reset variables
State = ""
City = ""

'Move the currently selected cell down one row
Selection.Offset(1, 0).Select


End Sub

Thank you for taking the time to look through my post!


02-11-2015, 10:03 AM
split(activecell.value,",")(0) gives first element in array. (1) gives second one = GA

so define variable as myresult = split(activecell.value,",")(1) & " - " & split(activecell.value,",")(0)

and put myresult in the cell you are processing.


02-12-2015, 08:15 AM
Awesome, that works great. Thank you Charlize!