Text Examples 3

Please purchase the course before starting the lesson.

Text Ex  9

The IT system has extracted the employee’s names in separate columns for First Name and Surname. You need to combine them in another format for the payroll. However, there appear to be a lot of problems with the data with extra spacing, inverted columns, comma’s and other problem characters. We need to create something so that each month we can just paste in the dirty data and it converts it to clean data.

  • ·         In Column C Combine the First Name and Surname into a single cell with a single space in between
  • ·         In cells D2 to I2 we have identified all the problem characters. In the yellow cells in column D to I, use the substitute formula to remove the unwanted character and replace it with nothing. Remember that each column should be based on the previous column so that the names get cleaner and cleaner.

If you discover other characters that need to be removed you can just add them to the next column.

Text Ex 10

You need to extract from the descriptions in cells B8 to B12, the date of the report to check whether they correspond with the month of the overall report shown in C6

Steps

1.     In C8 down, FIND where the first ‘/’ occurs

2.     In D8 down, extract the next 2 characters to pull out the month

3.     Depending on your settings you may need to convert the information in Column D into an Excel recognisable value in Column E (E8 down)

4.     In F8 down, check whether the month in column E is the same as the reporting month in C6 and say OK if it is and ERROR if it is not.

Text Ex 11

In this example we have extracted information from a PDF of phone records. We only want to extract the fax calls (has the words ‘Fax to EM’ in the text as per row 10. Furthermore, all we want to do is get the cost of the call which is the last few characters in the cell e.g. 6.09 in cell A10.

To see the full course contents click here.

Back to: Advanced Excel Course > Text Functions