Skip to content

Excel Challenge 4 – Text Functions

Welcome to your fourth Excel Challenge!

In this article, I will give you four Excel questions from easy to hard to test your knowledge of text functions.

Just click the link below to download the practice file for free. That way, you can test yourself to see how you do!

Excel Challenge 4 – Practice File

Question 1

For question 1, your task is to write a formula to get the first name from each full name.

Question 1 – Solution

There are a few different ways to accomplish question 1. For my example, I will be using the LEFT and FIND functions.

Start by selecting the cell next to the first full name, and enter the following formula.

=LEFT(B5, FIND(" ", B5) - 1)

The FIND function searches for the first instance of a space and returns the position. You then subtract one from the FIND result to get the position of the last letter of the first word from the full name.

The LEFT function then uses that position value to return that same number of characters from the referenced text. The result is the first name.

You can then use the fill handle to drag the formula down to return the first names for each full name in the list.

Question 2

For question 2, your goal is to write a formula to get the last name from each full name.

Question 2 – Solution

To solve question 2, I recommend you use the TEXTAFTER function paired with the TRIM function.

Select the cell next to the first full name, and enter the following formula.

=TEXTAFTER(TRIM(B5), " ", -1)

The TRIM function removes all spaces before and after the selected text. As a result, only the spaces between words remain.

The TEXTAFTER function then takes the trimmed text and returns all of the text after a specified delimiter. In this case, that delimiter is a space (the 2nd argument).

The negative one for the third argument forces the TEXTAFTER function to look for the first space from the right side of the original text string rather than from the left.

As a result, you end up with all of the text after the first space from the right, which gives you the last name.

You can then use the fill handle to drag this formula down for the remaining names in the list.

Question 3

For question 3, you must write a formula to generate the full name for each person.

 

Question 3 – Solution

The TEXTJOIN function is probably the best function to use to solve this problem.

Select the cell next to the first last name, and enter the following formula.

=TEXTJOIN(" ", TRUE, B5:D5)

The TEXTJOIN function combines all of the text strings from the selected range. The TRUE value signals the TEXTJOIN function to ignore any empty cells. And the first argument of the TEXTJOIN function specifies the delimiter or character that is to be placed in between each value from the referenced range.

You can then use the fill handle to drag this formula down for the remaining names in the list.

Question 4

For question 4, your goal is to write a formula to combine the title and last name of each person. Also, you must replace the title of ‘Doctor’ with ‘Dr.’

Question 4 – Solution

To solve question 4, you will use the TEXTJOIN function once again. You will also need to use the SUBSTITUTE function.

Select the cell next to the first last name, and enter the following formula.

=TEXTJOIN(" ", TRUE, SUBSTITUTE(B5, "Doctor", "Dr."), C5)

Just like before, the TEXTJOIN function combines the text values from the referenced cells and separates them with a space.

The big difference this time is that the SUBSTITUTE function is used to search for the text of “Doctor.” If the SUBSTITUTE function finds the text of “Doctor,” then it replaces that text with “Dr.”

As a result, you get back your desired result.

You can then use the fill handle to drag this formula down for the remaining names in the list.

How Did You Do?

Leave me a comment below to let me know how you did! Were these questions easy? Were they difficult? Were they somewhere in the middle?

Let me know your thoughts! I’d love to hear what you have to say.

Also, if you missed any of the Excel Challenges prior to this you, then you can check them out using the links below.

Watch the Video

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top