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.

img1

  • 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)

img2

  • 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.

img3

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube