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