Function Problems

How to Convert City Name Text to City of Format in Excel

Phi Hung Vo Sep 29, 2026 868 views

Question details

The user needs to reformat a text string containing a city name and a suffix in parentheses, converting "City Name (City of)" to the standard "City of City Name" format.

How to Convert City Name Text to "City of" Format in Excel

Product: Excel
Device & OS: not provided
Scenario: Standardizing address or location data where the city prefix has been appended at the end of the text string within parentheses.
Observed behavior: The current entries are formatted backwards with parentheses (e.g., "Carrizo Springs (City of)") and need to be rearranged into standard prefixes.

Before you start

Verify that your list of city names consistently uses the same suffix pattern (such as " (City of)") and identify the exact cell reference of your first data entry before applying formulas.

Solution 1Recommended

Use TEXTBEFORE Formula for Consistent Entries

A fast and straightforward concatenation method ideal when every entry perfectly matches the " (City of)" format.

If your data strictly contains the word "City of" inside the parentheses for every row, you can hardcode the prefix and dynamically pull just the city name. The TEXTBEFORE function is excellent for extracting everything before the parenthesis.

Select the destination cell

Click on an empty cell in a new column adjacent to your data, for example, cell B2 if your first city name is in A2.

Enter the formula

Type the formula ="City of "&TEXTBEFORE(A2," (") into the formula bar and press Enter.

Apply to the entire column

Select cell B2, hover over the bottom-right corner to see the fill handle, and double-click or drag it down to apply the formatting to the remaining entries.

Use TEXTBEFORE Formula for Consistent Entries

Compatibility Note: The TEXTBEFORE function is available in newer versions of Excel and Office 365. If you are using an older version, use the LET method below instead.

Solution 2

Use LET, MID, and FIND for Dynamic Extraction

A robust formula that dynamically extracts whatever text is inside the parentheses and places it at the front, regardless of the exact wording.

Format Text Instantly in WPS Spreadsheet

Easily Manipulate Text and Standardize Data with WPS Office

WPS Spreadsheet provides powerful text functions and complete compatibility with Excel's formula syntax, allowing you to quickly rearrange strings, extract data, and clean up messy datasets without hassle.

1. Open your data in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load the document containing your incorrectly formatted city names.
2. Apply the text formula: In an adjacent column, type the extraction formula (such as the LET or TEXTBEFORE combinations) to rearrange your text.
3. Drag to fill: Select the cell with the applied formula and double-click the bottom-right corner to instantly reformat the entire column.
4. Copy and Paste as Values: Select the new column, press Ctrl+C, then right-click and choose 'Paste as Values' to remove the formulas and keep the clean text.

100% compatibility with Microsoft Excel formulas and functionsBuilt-in Flash Fill to automatically detect and extract text patternsLightweight, fast, and completely free to useUser-friendly interface for effortless data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TEXTBEFORE formula return a #NAME? error?

This error occurs if you are using an older version of Excel that does not support the newly introduced TEXTBEFORE function. In this case, use the LET and MID formula alternative, or use a combination of LEFT and FIND.

Can I format this text without using formulas at all?

Yes. You can use the 'Flash Fill' feature. Simply type the corrected format 'City of Carrizo Springs' manually in the adjacent cell next to your first entry, press Enter, and then press Ctrl + E. The software will recognize the pattern and automatically fill in the rest.

How do I remove the formulas and just keep the corrected text?

Once you have successfully generated the new text in a column, highlight those cells and copy them (Ctrl+C). Then, right-click the same selection and choose 'Paste as Values' (usually represented by an icon with '123'). This replaces the formulas with standard text.

What if some of my city names do not have parentheses?

Formulas relying on FIND("(") will return a #VALUE! error if parentheses are missing. You can wrap your entire formula in the IFERROR function, like =IFERROR([YourFormula], A2), so that it just outputs the original city name if no parentheses are found.

Excel & Data Problems