Text Function: Concatenate,Replace and Substitute


CONCATENATE function is useful to join two or more string. REPLACE function is useful to replace one part of string with other part and SUBSTITUTE function is used to replace the old text string with the new text string

How to use CONCATENATE function in Excel?

The Excel CONCATENATE function is used to join series of text strings or values into a combined text.


=CONCATENATE( text1, [text2], … )

Where the text arguments are a set of one or more text strings or other values that you want to join together.


For example, CONCATENATE function is used to join string in cell A1 and cell B1. Provide Space between two words using "" (Quotation mark).

=CONCATENATE (A1, "", "B1)


How to use REPLACE function in excel?

The REPLACE function is used to replace a part of the text string with the new string.


    =REPLACE( old_text, start_num, num_chars, new_text )

old_text : The original text string that you want to replace.

start_num: The position within the old_text , the first character that you want to start with.

num_chars: The number of characters to replace.

new_text: The replacement text.

Here we are replacing last 4 characters of "Australia" with "Asia"


 How to use SUBSITUTE function in excel?

The SUBSITUTE function in Excel is used to replace the old text string with the new text string.


=SUBSTITUTE( text, old_text, new_text, [instance_num] )


text: The original text string.

old_text: The text which is to be replaced by the new text.

new_text: The new text with replaces the old text.

[instance_num]: An optional argument which specifies which occurrence of old_text should be replaced by the new_text. If [instance_Num] is specified, just that instance of the old_text is replaced .Otherwise, all instances of old_text are replaced with the new_text.



Note: REPLACE function is used when you know the position of the characters which you want to remove. Use it when you want to replace fix part of your string. SUBSTITUTE function is used to replace the whole string with other string.

If you find this useful, you can stay connected with us on facebook to get more amazing tips and tricks of Microsoft excel.

13,881 total views, 17 views today

One thought on “Text Function: Concatenate,Replace and Substitute

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>