Text Examples 2

Please purchase the course before starting the lesson.

Text Ex 6

Using a formula (not a tool), split column A so that we can see the account number and account description separately.

Text Ex 7

The IT system has extracted the employee’s names in a single column shown as SURNAME then FIRST NAME but you need it split between First Name and Surname and to clean up the presentation.

  • In column B change the case so that it appears in Proper Case i.e. FILLIES ANTHONY appears as Fillies Anthony.
  • In Column D, FIND where the space occurs in the name (which tells us where the first name and surname split)
  • In Column E, extract the First Name
  • In column F extract the surname

Text Ex 8

The IT system has extracted the employee’s names in a single column shown as SURMANE FIRST NAME but you need it split between First Name and Surname and clean up the presentation. In this case you have some names with more than one space (double barrel names)

  • In column B change the case so that it appears in Proper Case i.e. FILLIES ANTHONY appears as Fillies Anthony.
  • In column C, find how many spaces are in column A. In order to do this think about using the LEN and Substitute function
  • In Column D, find where the space occurs in the name (which tells us where the first word and second word split)
  • In Column E, extract the First Name
  • In column F extract the surname
  • Switch on the Data Autofilter and investigate any row with more than 1 space. Make the correction in the data (this is unfortunately generally a manual process as double barrel names occur in first names, surnames and sometimes both).

To see the full course contents click here.

Back to: Advanced Excel Course > Text Functions