Combine texts together/’&’, CONCATENATE

To combine words/texts, we use the following formula, function.

=text1 & text2 & [text3]…

[text1] = Identify 1st word to combine.
[text2] = Identify 2nd word to combine.

Definition of formula A formula to combine specified words.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

=CONCATENATE(text1, text2, [text3]…)

[text1] = Identify 1st word to combine.
[text2] = Identify 2nd word to combine.

Definition of function A function that combines specified words.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

There are 2 ways to combine texts:

・Set up a formula with the symbol ‘&’
・Use the CONCATENATE function

Example of use) Set up a formula with the symbol ‘&’, CONCATENATE function

Below is a list of participants for a table tennis summer camp. We would like to combine the address in column C and D into column E.
We will use 2 ways to combine them, the symbol ‘&’ and the CONCATENATE function.

① Use the symbol ‘&’ to combine address.

(1) Input ‘=’.
(2) Combine 1st cell ‘C4’ with 2nd cell ‘D4’ with the symbol ‘&’.
=C4&D4

② Use the CONCATENATE function to combine address.

(1) [text1] = Select 1st cell ‘C5’.
(2) [text2] = Select 2nd cell ‘D5’.
=CONCATENATE(C5&D5)

 

POINT!
The results are the same for when using the symbol ‘&’ and the CONCATENATE function, so either method is fine. The only difference is that the CONCATENATE function allows a maximum of 30 words to combine.

 

Extract, divide texts/ LEFT, MID, RIGHT

To extract a specific word from a group of texts, we use the following function.
We often use this function to delete unnecessary text in data.

=LEFT(text, [num_chars])

[text] = Identify the text group that includes the word to be extracted.
[num_chars] = Identify the number of letter in word to extract.

Definition of function A function that extracts words based on the [num_chars] counting from the ‘left end’.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MID(text, start_num, num_chars)

[text] = Identify the text group that includes the word to be extracted.
[start_num] = Identify the location of the first letter of word to extract, counting from the left.

[num_chars] = Identify the number of letters of word to extract.

Definition of function A function that extracts words based on the [start_num] with the [num_chars].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=RIGHT(text, num_chars)

[text] = Identify the text group that includes the word to be extracted.
[num_chars] = Identify the number of letters of word to extract.

Definition of function A function that extracts words based on the [num_chars] counting from the ‘right end’.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

The 3 functions can be used differently according to these situations.

・Extract words from the left end (of text group) → LEFT function
・Extract words from the middle (of text group) → MID function
・Extract words from the right end (of text group) → RIGHT function

 

Example of use) Extract text/LEFT, MID, RIGHT function

In the table below, the whole address is displayed in column C. We would like to divide the address into ‘number’, ‘ (the address) other than the state’ and ‘state’ between column D~F.
We will use the 3 functions to extract each information (the number, address other than state, and state name) from the whole address.

① Use the LEFT function to extract the ‘number’ on the left end.

In the address above, the number has only one character, so we set up a formula to extract the 1st character from the whole address.
(1) [text] = Identify text group that includes the character to be extracted = cell ‘C4’.
(2) [num_chars] = The character to extract is the 1st letter counting from the left so we input ‘1’.
=LEFT(C4,1)

② Use the MID function to extract the address other than the ‘number’ and ‘state’.

The address other than the ‘number’ and ‘state’ has 21 letters beginning from the 2nd letter.
(1) [text] = Identify text group that includes the words to be extracted = cell ‘C4’.
(2) [start_num] = The words to be extracted begins from the 2nd letter, so we input ‘2’.
(3) [num_chars] = The words to be extracted ends with the 23rd letter, so we input ’23’.
=MID(C4,2,23)

③ Use the RIGHT function to extract the ‘state’ on the right end.

Lastly, we will extract the ‘state’ name in the right end.
(1) [text] = Identify text group that includes the word to be extracted = cell ‘C4’.
(2) [num_chars] = The word to extract contains 8 letters counting from the right, so we input ‘8’.
=RIGHT(C4,8)

When identifying the [num_chars], ‘spaces’ are also included as a character/letter.

Search and replace characters/SUBSTITUTE, REPLACE

To search and replace words/characters, we use the function below. We use the SUBSTITUTE function when identifying character and replacing with others, and use the REPLACE function when identifying the location of word and replacing with others.
=SUBSTITUTE(text, old_text, new_text, [instance_num])

[text] = Identify text group that includes the word to be replaced.
[old_text] = Identify old word to be replaced.
[new_text] = Identify new word to replace.
[instance_num] = Not obligatory. Identify the location of word wished to be replaced counting from the left, when there is more than one [old_text]. If nothing is written here, all of the words will be replaced.

Definition of function A function to replace the [old_text] to a [new_text].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=REPLACE(old_text, start_num, num_chars, new_text)

[old_text] = Identify text group that includes the old word wished to be replaced.
[start_num] = Identify the location of the first letter of word wished to be replaced.
[num_chars] = Identify the number of letters to replace.
[new_text] = Identify the new word to replace.

Definition of function A function to replace the [old_text] to a [new_text] from the [start_num] based on the [num_chars].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use 1) SUBSTITUTE function/Search and replace characters

In the table below, all of the participants live in ‘California’, but we accidently typed in the country name so we must change it. We will use the SUBSTITUTE function to replace the country name with the state name.

① Use the SUBSTITUTE function to change ‘United States’ to ‘California’.

(1) [text] = Identify text group that includes the word to be replaced, cell ‘C4’.
(2) [old_text] = Surround ‘United States’ with ” (double quotations). → “United States”
(3) [new_text] = Surround ‘California’ with ” (double quotations). → “California”
(4) [instance_num] = Because there is no same word as the [old_text], we do not input anything.
=SUBSTITUTE(C4,”United States”,”California”)

 

POINT!
When there are multiple of the same words that apply as the [old_text] in the SUBSTITUTE function, we can identify which word to replace in the [instance_num].

Example of use where we use the [instance_num])

In cell A1, there is the sentence “Adventures of white cat Elly and white cat Pat”, and we would like to change ‘white cat Pat’ to ‘black cat Pat’. (i.e. changing the 2nd word of ‘white’ to ‘black’)
→ =SUBSTITUTE(A1,”white”,”black”, 2 )

 

Example of use 2) REPLACE function/Identifying the location of the word

Now we will use the table below to explain how to use the REPLACE function.

① Use the REPLACE function to change ‘United States’ to ‘California’.

(1) [old_text] = Select cell with word to be replaced, ‘C4′.
(2) [start_num] = The word to be replaced begins from the 24th letter, so we input ’24’.
(3) [num_chars] = The word to be replaced contains 13 letters counting from the [start_num], so we input ’13’.
(4) [new_text] = Surround new word ‘California’ with ” (double quotations) → “California”
=REPLACE(C4,24,13,”California”)

 

POINT!

The SUBSTITUTE function and the REPLACE function have different syntax but they both do the same job of replacing words/characters.
However, the main difference is that

・ the SUBSTITUTE function contains the [old_text] which allows us to directly identify the word to be replaced.
・ On the other hand, the REPLACE function uses the location rather than identifying the word itself.

When there are many different words to replace, the REPLACE function might be more efficient.