Clean Up Functions
There are many functions in Excel over 300 in fact. There will be times when you will import data from other systems into Microsoft Excel and you will want to tidy up that data. For example, extra spaces at the beginning, end, or in between text is a popular scenario use the TRIM function to resolve this issue. This will ensure no errors when this data is used in other functions.
TRIM, UPPER, LOWER, PROPER, LEFT, RIGHT, MID, LEN, TEXT, CONCATENATE
The trim function will take out extra space from the beginning, end or in between words.
This function will change all the text in a cell to upper case. You may find that you will need to combine functions this is called created Nested Functions a function inside another function.
In this instance due to the spaces we will need to combine the TRIM Function with the UPPER Function.
For the functions LOWER and PROPER you can follow the same structure as above and simply replace the function name UPPER with LOWER or PROPER.
The function PROPER capitalized the first letter of every word.
=LOWER(A1) or =PROPER(A1)
PROPER analir pisani will become Analir Pisani
excel courses sydney will become Excel Courses Sydney
The function for LEFT will display the first x number of characters you nominate starting from the left. The functions starts with = followed by the Functions Name LEFT then the open bracket in that order.
Select the cell which contains the text followed by a comma. The comma is important it says, now tell me how many characters starting from the left you would like to display followed by a closing bracket.
Analir =LEFT(A1,3) result is Ana
Remember if there is a space at the beginning of Analir then you will end up with na. This example below has an intentional space in front of Analir. Hence using the TRIM function is going to be necessary to tidy up the result to what you need.
Analir = LEFT(TRIM(A1),3)
The function for RIGHT is exactly the same as the function for LEFT with the exception that this function will return counting from the right.
The function for MID will return characters from the middle you will need to select the cell with the characters to start from. You will then need to state the starting point by telling me how many jumps to the right. The count start from the number you stated eg 2 in my example jump 3 characters to the right from the letter n. Followed by a comma then stating the no of characters you would like to display.
=MID(text,Start, No Characters)
=MID(A1,2,3) result is nal
The function for LEN means length of characters. This will count the number of characters in a cell including any spaces. This is great when you need to ensure that the product code is X no of characters long other wise it can’t be upload back into the system. Ensuring you have all the characters for your ABN, ACN, Bank Account no. SKU number, Batch numbers, ID’s and security passes.
All these fields often require to be a specific number of characters. We often interact with other systems like filling out forms on the web, import/export data to varies locations and if these fields don’t match a specific number of characters other related systems and functions might return errors.
Press = LEN then open bracket (
analir result is 6
The CONCATENATE function joins together contents from other cells. For example you get 3 numbers and 4 text entries but they are in separate cells. You want to join them together to make up a new code. Perhaps you are wanting to perform a VLOOKUP function but there is no unique data, what you can do here is join a persons first and surname with an email address to make a unique field. If you join a text field with a data field or money field it will display in its raw format. For example the date 10/8/2000 will display as 36748. To retain the date formatting you will need to add the functions for TEXT, adding the TRIM function is also a good idea as this will clean up any extra spaces at the beginning of end of the cell.
AZ Solutions Pty Ltd est joined with 10-8-2019 will display as AZ Solutions Pty Ltd est36748
Join Cell A1 with B1
When joining cells there is no space between the contents of cell A1 and cell B1, using ” ” will give you a space between them. You can also enclose any symbol or text you want between the quote marks, for example “-” , “.” , “client name”.
Joining cells can also be done by using the & sign. It is still a function so start with = followed by a cell ref then & and next cell reference.
Analir in cell A1
Pisani in cell B1
=A1&B1 result is AnalirPisani
Notice there is no space between the two, add ” ” for a space between Analir and Pisani. Importantly you will need an other & sign. Think of the & sign as the glue between what is on the left and right of it.
=A1&” “&B1 result is Analir Pisani