If you need to insert a word in a string using Excel formulas the position where this should happen must be determined first. Let’s say the word “fresh” must be inserted in the string “Growing a green apple tree in a green field.” before the word “apple”.
Inserting a word in a string
First determine the position of the word “apple” in the string using the find() formula as in the picture:
After this the string must be broken in 3 parts: the left side before the inserted word, the new word and the remaining string. The right side of the string is determined using the RIGHT() formula, starting at the position of the insertion, which is calculated as the total length of the string less the position of the word “apple” +1.
Concatenating before and after insert
In the final step the three sub strings must be concatenated using “&” operator:
=LEFT(“original string”,FIND(“apple”,”original string”)-1)&”fresh “&RIGHT(“original string”,LEN(“original string”)-FIND(“apple”,”original string”)+1)
Don’t forget to insert a space after the word “fresh “, but inside the quotation marks.