Replacing Substrings with Numbers from Adjacent Cells in Microsoft Excel 2010

If you want to replace substrings with specific number of text from adjacent cell,you can use a combination of   "FIND", "LEFT" & "MID" functions.
LEFT: Returns the specified number of characters from the start of a text string.

Syntax: =LEFT(text,num_chars)

text: It is the text string that contains character that you want to extract

num_chars: It specifies the number of characters from left you want to extract.
MID: Returns the characters from the middle of a text string, given a starting position and length.

Syntax: =MID(text,start_num,num_chars)

text: It is the text string that contains the characters you want to extract.

start_num: The number in the string from which you want to extract data.

num_chars: Itspecifies the number of characters you want from MID to retrieve or extract.
Let us take an example:

We have some strings that contain “XX” as a substring in column A. In column B, we have text which needs to be replaced with XX.

• We want a formula that will replace the substring i.e. “XX” in corresponding cell in column A & replace with the text in column B
• In cell C2, the formula would be
• =LEFT(A2,FIND("XX",A2)-1)&B2&MID(A2,FIND("XX",A2)+2,255)

• In above shown screenshot, the formula has replaced the text “XX” with “C”.
• Copying down the formula in below range, we will get the desired result.

This is the way we can replace substring with numbers from adjacent cells in Microsoft Excel.