Excel functions: 7 ways to use Text functions to manage data

How to replace words; extract first and last words, matched or a specific words in a string of text

Credit: Rob Schultz

Excel’s Text functions are a major time saver if your job entails managing massive data, especially data that’s imported from other sources. Fortunately, all ASCII data is easily imported, but the format of that imported data can vary drastically from one source to another.

For example, fields may be delimited, such as  with tabs, spaces, commas, or periods. Aany other characters are all preferable to spaces. That’s because spaces not only exist in between fields, they also separate words within the fields, which makes sorting out the fields a real challenge. That’s where the following Text functions are very helpful.

A. Use the SUBSTITUTE function to replace one string of text with another

Function syntax: The syntax (or sentence structure) of the SUBSTITUTE function is this:

=SUBSTITUTE(text, old text, new text, [Instance Num]).

Note: If you don’t specify an Instance Num, every occurrence of the Old Text is changed to the New Text. If you specify the Instance Num, only that occurrence of the Old Text is replaced. For example, entering the number ‘1’ means you want to change only the first occurrence of that word in the string. 

1. Enter some phrases in column A (from A2 through A13).

2. Enter the word or phrase you want changed in column B.

3. Enter the word or phrase you want to replace the old text with in column C.

4. Enter the following formula in cells D2 through D7 (or half the database): =SUBSTITUTE(A2, B2,C2,1)

This changes/replaces only the first occurrence of the Old Text to New Text.

5. Next, enter this formula in the remaining cells (in our case, D8 through D13): =SUBSTITUTE(A2, B2,C2). This changes/replaces all occurrences of the Old Text to New Text.

Note: This function is case-sensitive, so if your results aren’t working, change the text to all the same case.

01 use the substitute function to replace one string of text with another PC World / JD Sartain

01 Use the SUBSTITUTE function to replace one string of text with another

B. Extract the last word in a string of text using TRIM, RIGHT, & SUBSTITUTE

For this example, the object is to extract the last word—that is, the last name, from a string of text (the full names of a list of clients).

1. Enter some names in column A: first, last, and middle names or initials.

2. Enter this formula in B2: =TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(“ “,50)),50)).

3. Copy the formula from B2, down to B3 through B1000 (or the end of your database). For this example, we're assuming your database has 1,000 records.

4. This formula works because the SUBSTITUTE function locates all the spaces in the string of text, and then replaces each single space with 50 spaces. The RIGHT function removes 50 characters (from right to left), and the TRIM function deletes all the excess leading spaces leaving just the single, last word. If you have longer strings of text, try substituting 100 or more for the 50 values in the above formula.

02 extract the last word in a string of text PC World / JD Sartain

Extract the last word in a string of text

C. Extract the first word/name in a string of text using LEFT & SEARCH

This formula works when you need to separate the first name from the middle and last name of a list of clients.

1. Enter some names in column A (or use the same names from the previous exercise).

2. Enter this formula in B2 through B1000: =LEFT(A2,SEARCH(“ “,A2)-1) to extract the first name of each client into a separate column.

03 extract the first word name in a string of text PC World / JD Sartain

Extract the first word/name in a string of text

D. Extract everything except the first word in a string of text using TRIM, RIGHT, REPT, & SUBSTITUTE

The purpose of this exercise is to remove the honorifics from a list of client names. These clients are providing confidential survey information, so the company does not want the titles and salutations of each individual to influence the surveyors.

1. Enter some more names in column A (or use the same names from the previous exercise). Enter some honorifics before each name; e.g., Mr., Miss, Ms, Mrs. , Dr., Sir, Lord, Lady, Capt., etc.

2. Enter this formula in B2 through B1000: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2),” “,REPT(“ “,60)),180)) to extract the full names of all the clients minus the honorifics.

3. And, if you wanted to extract the honorifics (for some reason), enter this formula in C2 through C1000: =LEFT(A2,SEARCH(“ “,A2)-1).

04 extract everything except the first word in a string of text PC World / JD Sartain

Extract everything EXCEPT the first word in a string of text

E. Extract names from email addresses using LEFT, FIND, & SUBSTITUTE

The worst job of the day is to spend hours manually retyping client names or domain names from email addresses, especially when the list is over 5,000 names. Use the following formulas to complete this task in minutes.

1. Enter some email addresses in column A.

2. Enter this formula in B2 through B5000: =LEFT(A2,FIND(“@”,a2)-1) to extract the full names of all the clients.

3. Enter this formula in C2 through C5000 to remove the underscore between the first and last name: =SUBSTITUTE(B2,” “,”_”).

4. Move to cell F2. Select FormulasText > . Type C2 in the Text field box on the Functions Arguments dialog screen, or click cell C2, and then click OK. This formula converts the names to Proper Case (that is, first letter of the first and last name capitalized, all other letters in lowercase).

5. Copy the formula in F2 to F3 through F5000 and press Enter.

05 extract names from email addresses PC World / JD Sartain

Extract names from email addresses

G. Extract domains from email addresses using TRIM, LEFT, SUBSTITUTE, MID, FIND, LEN, & REPT

1. Enter this formula in D2 through D5000 to extract the domain names from the email addresses:

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(“@”,A2),LEN(A2)),” “,REPT(“ “,100)),100))

2. And last, enter this formula in E2 through E5000 to remove the @ signs from the extracted domain names: =SUBSTITUTE(D2,”@”,””).

06 extract domains from email addresses PC World / JD Sartain

Extract domains from email addresses

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags ExcelOffice 2013Office 2016

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

JD Sartain

PC World (US online)
Show Comments

Father’s Day Gift Guide

Brand Post

PC World Evaluation Team Review - MSI GT75 TITAN

"I need power and lots of it. As a Front End Web developer anything less just won’t cut it which is why the MSI GT75 is an outstanding laptop for me. It’s a sleek and futuristic looking, high quality, beast that has a touch of sci-fi flare about it."

Most Popular Reviews

Latest Articles

Resources

PCW Evaluation Team

Luke Hill

MSI GT75 TITAN

I need power and lots of it. As a Front End Web developer anything less just won’t cut it which is why the MSI GT75 is an outstanding laptop for me. It’s a sleek and futuristic looking, high quality, beast that has a touch of sci-fi flare about it.

Emily Tyson

MSI GE63 Raider

If you’re looking to invest in your next work horse laptop for work or home use, you can’t go wrong with the MSI GE63.

Laura Johnston

MSI GS65 Stealth Thin

If you can afford the price tag, it is well worth the money. It out performs any other laptop I have tried for gaming, and the transportable design and incredible display also make it ideal for work.

Andrew Teoh

Brother MFC-L9570CDW Multifunction Printer

Touch screen visibility and operation was great and easy to navigate. Each menu and sub-menu was in an understandable order and category

Louise Coady

Brother MFC-L9570CDW Multifunction Printer

The printer was convenient, produced clear and vibrant images and was very easy to use

Edwina Hargreaves

WD My Cloud Home

I would recommend this device for families and small businesses who want one safe place to store all their important digital content and a way to easily share it with friends, family, business partners, or customers.

Featured Content

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?