0

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?

Improve this question
2
  • 4
    So, what did you try?
    – Hannu
    Commented Mar 16, 2023 at 16:29
  • 1
    there is a bunch of text manipulation formulas in excel. (Formula -> function library -> text functions). You'll have to combine several of these functions to get what you want.
    – gns100
    Commented Mar 16, 2023 at 17:39

2 Answers 2

Reset to default
0

LEFT & RIGHT Approach

This approach uses the LEFT and RIGHT functions to find the two text strings. TRIM removes any trailing and following white spaces.

=TRIM( RIGHT(A1,LEN(A1)-FIND(",",A1)) & ";" & " " & 
       LEFT(A1,FIND(",",A1)-1) )

INDEX & TEXTSPLIT Approach

This approach uses TEXTSPLIT to separate the string based on commas, then uses INDEX to select the component parts for joining with &. TRIM removes any trailing and following white spaces.

=TRIM( INDEX(TEXTSPLIT(A1, ","),, 2) & ", " & 
       INDEX(TEXTSPLIT(A1, ","),, 3) & "; " &
       INDEX(TEXTSPLIT(A1, ","),, 1) )
Improve this answer
0

Assuming no version constraints based on your tags:

=TRIM(TEXTJOIN("; ",,TEXTAFTER(A1,","),TEXTBEFORE(A1,",")))

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .