When you have two or more cells with values that you need to merge in one cell, there are several ways to do this in MS Excel.
Method 1: Using CONCATENATE
The CONCATENATE function is used by identifying the cells which will be combined as one. It is simply done as seen in the image below.
You can also add other characters in the string by enclosing them inside quotation marks. In the example below, a space is added to separate the first name and the last name.
Method 2: Using the ampersand
The ampersand (&) can also be used to combine values of cells. The ampersand is used like an operator, acting as if a glue that combines the cells or values (inside quotation marks as well.) The example below demonstrates how to use ampersand to combine texts.
There are new functions in Excel 2019 that can also combine texts. Read about them here.
When you have a string of texts and numbers and you need to extract characters out of it, you can use the LEFT, RIGHT, and MID functions.
LEFT and RIGHT have two parameters: the cell where the values will be taken from, and the number of characters to extract either from LEFT or RIGHT, respectively. (See image, column B and C.)
On the other hand, MID has a middle parameter (Column D of image) that determines at what position it should get characters from. Remember that unlike LEFT and RIGHT that has a fixed starting position as to where to get characters from, MID starts somewhere in the middle, therefore, requiring this parameter that points out where it should begin getting characters from the left side.
In the example:
The 6 represents the 6th character from the value:
The 3 is num_chars. From the start_num, how many characters will be extracted:
When you need to edit the texts inside cells, you can use the UPPER, LOWER, and PROPER functions of MS Excel.
Simply start with the function, open parenthesis, then click the cell where the text is.
UPPER function makes all letters capitalized or in upper case.
LOWER function makes all letters in lower case.
PROPER function makes the first letter of each word capitalized. The word may be separated with any delimiter like comma, periods, colons, etc.-- not just space.
firstname.lastname@example.org will become John.Doe@Gmail.Com if it passes through a PROPER function.