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.



Example:


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>