Excel Text functions: trim, value, lower, upper, concatenate
Excel Function TRIM
The TRIM() function is used to remove all the white spaces from the given string.
The main thing is that, the space between two consecutive words are not removed.
This is most helpful when there are white spaces before or after a word which is not desirable.
| Text | TRIM()` | FORMULA | REMARKS |
| ha | ha | =TRIM(L4) | First char white space removed |
| hello anderson | hello anderson | =TRIM(L5) | white space between two words not removed. |
| Hi dia | Hi dia | =TRIM(L6) | Last char white space after "dia" removed. |
| Hi dia ? | Hi dia ? | =TRIM(L7) | white space between the words not removed. |
| ms office is good . | ms office is good . | =TRIM(L8) | white space between the words not removed. |
Excel Function VALUE
The VALUE() function converts the given text into numerical value.
The text should also be some form of numeric, such as number, date, time and so on.
The function interprets the type of number specified and returns its numerical value.
| Text | TEXT() | FORMULA |
| 02/12/2018 | 43436 | =VALUE(L17) |
| 0.579305555555556 | 0.579305555555556 | =VALUE(L18) |
| 1000 | 1000 | =VALUE(L19) |
| 1.2E+34 | 1.2E+34 | =VALUE(L20) |
| 0.2 | 0.2 | =VALUE(L21) |
Excel Function LOWER
The LOWER() function is used to convert the specified string into lower case.
This function takes in a single parameter; a string.
If the string contains other characters apart from the alphabets, they are ignored.
| Text | LOWER() | FORMULA |
| BIRD | bird | =LOWER(L29) |
| Bird12 SINGS | bird12 sings | =LOWER(L30) |
| 3455 | 3455 | =LOWER(L31) |
| MAN"90 | man"90 | =LOWER(L32) |
| That's MiNE | that's mine | =LOWER(L33) |
Excel Function UPPER
The UPPER() function is used to convert the specified string into upper case.
This function takes in a single parameter; a string.
If the string contains other characters apart from the alphabets, they are ignored.
| Text | LOWER() | FORMULA |
| bird | BIRD | =LOWER(L41) |
| bird12 sings | BIRD12 SINGS | =LOWER(L42) |
| 3455 | 3455 | =LOWER(L43) |
| man"90 | MAN"90 | =LOWER(L44) |
| that's mine | THAT'S MINE | =LOWER(L45) |
Excel Function CONCATENATE()
The CONCATENATE() function is used to combine two or more strings.
This function takes several stings and joins them into a single string.
The maximum strings that is permissible is limited to 255.
| Text 1 | Text 2 | CONCATENATE() | Formula |
| I am | sam | I am sam | =CONCATENATE(L53,M53) |
| This | my | This is my boy | =CONCATENATE(L54," is ",M54," boy") |
| Hello | kate | Hello kate | =CONCATENATE(L55,M55) |
| WOMAN | wom | WOMANwom | =CONCATENATE(L56,M56) |
| lady | lady | ladylady | =CONCATENATE(L57,M57) |