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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.