Turn REPLACE function into “INSERT” function

Sometimes, we want to add a text string, say “XX”, as a prefix or suffix to another text string.  This is quite easy with the function CONCATENATE, or even easier with the operator &.  However, what if we want to insertthe text string into a specific position in the middle of another text string ??

The function REPLACE comes to rescue.

umm… REPLACE?

According to its description,

it replaces (not inserts) part of a text string with a different text string.

Shall we use a FUNCTION called “INSERT”?  Unfortunately there is no such “INSERT” function, as REPLACE could do it for us.  Let’s understand the syntax of REPLACE

REPLACE(old_text, start_num, num_chars, new_text)

• Old_text    Required. Text in which you want to replace some characters.
• Start_num    Required. The position of the character in old_text that you want to replace with new_text.
• Num_chars    Required. The number of characters in old_text that you want REPLACE to replace with new_text.
• New_text    Required. The text that will replace characters in old_text.

Source: Office Support

Well, having read the syntax and the explanation for each argument, we know that REPLACE can be used to convert “AAA-BBB-CCC” to “AAA-XXX-CCC” easily.  Let’s use our example to illustrate the function:

=REPLACE(A3,5,3,"XXX")

'where A3 resides the text string "AAA-BBB-CCC"

How it works?

1. The formula looks into the text string “AAA-BBB-CCC’ in A3 (first argument: A3)
2. Starting at the fifth position (second argument: 5)
3. Replace three characters starting from there, i.e. “BBB” (third argument:3)
4. with “XXX” (final argument: “XXX”)

As a result, “AAA-BBB-CCC” becomes “AAA-XXX-CCC”

Straight-forward.  Isn’t it?

But talking about to our task:

How to insert “XX” into a specific position of another text string?

No worry.  A simple trick turns REPLACE into “INSERT”.

And the trick is…… to put 0 as the third argument.

Let’s see how the following formula works:

=REPLACE(A3,5,0,"XX-")

'note the "-" at the end

'where A3 resides the text string "AAA-BBB-CCC"

1. The formula looks into the text string “AAA-BBB-CCC’ in A3 (first argument: A3)
2. Starting at the fifth position (second argument: 5)
3. Replace zero character, i.e. nothing (third argument: 0)
4. with “XX-” (final argument: “XX-“)

As a result, “AAA-BBB-CCC” becomes “AAA-XX-BBB-CCC”.

When we replace “nothing” with “something” at a specific position, we practically insert“something” at that position.  Make sense?  🙂