How capture City and State values from one cell (with a delimeter character)

[ravl13]

Hello,

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
    Sheets("Sheet1").Select
        
        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.
        Range("B1").Select
        
        '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 = TheTwoCharactersToTheRightOfTheCommaMinusThePrecedingSpaceCharacterInSelectedCell
           
        '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
             
        Loop




End Sub

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

-ravl13

[Charlize]

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.

Charlize

[ravl13]

Awesome, that works great. Thank you Charlize!