Reformatting words in a cell

Question

In cell A1, it says "269 Southwest 13th Avenue, Pompano Beach, FL".

In cell A2, I want it to be reformatted to "Pompano Beach, FL; 269 Southwest 13th Avenue".

What formula would I use to reformat?

Answer 1

=TRIM(MID(A1,FIND(",",A1)+2,1000))&"; "&LEFT(A1,FIND(",",A1)-1)

There's always more than one way to do things in Excel. This video shows one way. Please let me know if you have any questions.

Answer 2

You can write custom worksheet functions in VBA. The function below will accomplish this task.

Function ReverseParts(Input_String As String, _

Input_Separator As String, _

Output_Separator As String) As String

Dim Index_First As Integer

Dim Index_Last As Integer

Dim Parts() As String

If Input_Separator = "" Then

' The Split function can't handle Input_Separator = "".

ReverseParts = "#No Input Separator Given"

ElseIf InStr(Input_String, Input_Separator) = 0 Then

' Input_Separator not in Input_String.

ReverseParts = "#Input Separator not in Input String"

Else

' Split Input_String at 1st occurrence of Input_Separator.

Parts = Split(Input_String, Input_Separator, 2)

Index_First = LBound(Parts)

Index_Last = UBound(Parts)

If Index_Last <= Index_First Then

' Should be impossible, but just in case.

ReverseParts = "#Index_Last <= Index_First"

ElseIf Index_Last = Index_First Then

' Input_Separator not in the Input_String.

ReverseParts = Input_String

Else

' Input_Separator in the Input_String.

' Reverse the parts of the Input_String.

ReverseParts = Parts(Index_Last) & Output_Separator & Parts(Index_First)

End If

End If

End Function

Answer 3

Try this:

=MID(A1,SEARCH(",",A1) + 2,LEN(A1)) & ", " & LEFT(A1,SEARCH(",",A1)-1)

NOTE: This formula will work provided that the address is separated by a comma followed by a space AND it separates the strings to be reversed.