How to Replace Partial Strings in Excel

Replace partial strings in Excel: This technique is essential when updating parts of text strings without altering the entire content—perfect for standardizing data formats, abbreviating text, or cleaning up records to enable meaningful analyses.

Partial string replacement is about precision: changing just a portion of a text string while leaving the rest intact. We need to first identify the text (substring) that needs to be replaced, and then replace it with the new text.  

In this post, we will take it up to another level, by making the replacement text dynamic.  

Note: Carefully evaluating possibilities of patterns within the source data is extremely important. For example, if we search for a comma and use it to extract the parts of the string, we must be sure that the comma will appear only once in the right place. The more possibilities, the more complex our formula will become.  

Let’s take this sample dataset of city names (starting from cell F6).

We want to convert these by using state abbreviations instead of full names. For example,  

• Austin, Texas should be converted to Austin, TX 
• Los Angeles, California should be converted to Los Angeles, CA 

To do this dynamically, we need to know the abbreviations for each state name. We will store that in a separate table as shown below.  

Let us convert the state name and abbreviation data into a table.

To convert your data into a table, select the data (as a whole), press CTRL + T, and assign a name, here we’ll call this T_ABBR

Table conversion is something we always recommend to do before applying any function to increase formula readability and for scalability when the data expands.

We will accomplish our objective of replacing partial strings in 3 steps.

1. Extract just the full State name from our city value.
2. Look up the abbreviation for the full state name from our table.
3. Create the new city value by replacing the state name with an abbreviation.

We need a final output that looks like this:

Let’s assume the city name is in cell F6. We will write our formulas referencing that cell.  

Identifying the State Name

Use TEXTAFTER to isolate the state name following the city name.  

=TEXTAFTER(F6,”,”)

This formula will process the ‘Austin, Texas’ value, and return ‘Texas’ with the TEXTAFTER function.

Click and drag the formula from cell I6 to populate the rest of the cells as required.

Look up the abbreviation for the state name

=XLOOKUP(I6, T_ABBR[State Name], T_ABBR[Abbreviation])

This formula searches for the value ‘Texas’ in the Abbreviation table T_ABBR and returns the value ‘TX’

The XLOOKUP function here looks up a value in a range/array and returns a range/array based on the lookup value.

Replace the state name with the abbreviation

We’ve got the state names and the abbreviations, let us look at how to replace the City, State Name with the abbreviations.

=REPLACE(F6, SEARCH(“,”, F6)+2, LEN(I6), J6)

 Combine REPLACE, SEARCH, and LEN to replace the full state name with its abbreviation within the string.

REPLACE: Swaps a specific segment of a text string with a new string.

SEARCH: Locates the position of a text string within another string.

LEN: Counts the number of characters in a text string and returns the number

Let’s look at this step by step.

First, we find the location (within the city name Austin, Texas) where we should start replacing.  

SEARCH (“, “, F6) +2  

A comma followed by a space appears in the 7th position in the string. We add 2 to account for the comma and the space. So, the 9th position within the string is where ‘Texas’ begins. The result of this is 9.  

 Next, we need to determine how many characters we are replacing. This is nothing but the length of the state name. Texas has 5 characters.  

LEN(I6)

We now know where to start replacing (9th position), how many characters to replace (5), and also what to replace it with (TX abbreviation which we determined in the previous step).

We put this all together with the REPLACE function.  

=REPLACE(F6, SEARCH(“,”, F6)+2, LEN(I6), J6)

REPLACE function extracts cell F6 (Austin, Texas) and then replace ‘Texas’ with ‘TX’.  

Drag the formula to all the necessary cells to get the desired result.

Check our dedicated, step-by-step video on replacing partial strings in Excel:

Do check our post on handling strings and nulls in Excel.