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 *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>