Insert a text string into a specific position of another text string in #Excel

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

Excel Tips - Insert string

The function REPLACE comes to rescue.

Excel Tips - Insert string1

umm… REPLACE?

Excel Tips - Insert string2

According to its description,

it replaces (not insertspart 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?  🙂

Advertisements
This entry was posted in Formula and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s