How to Extract Text between Two Spaces in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with Excel datasets, users need to extract strings from entries. Excel extract text between two spaces is one of the scenarios where users need to fetch strings between spaces. Excel’s combined functions and features can extract text between spaces.

Let’s say, we have Customer and Company Name along with their Customer IDs. We want the First Names extracted from the entries.

Dataset-Excel Extract Text Between Two Spaces

In this article, we use combined functions such as MID and FIND, MID and SEARCH, SUBSTITUTE, MID, and REPT, TRIM, MID, and REPT as well as Text to Columns and Flash Fill features to extract text between two spaces.


How to Extract Text between Two Spaces in Excel: 5 Easy Ways

Method 1: Extract Text between Two Spaces Using MID and FIND Functions

Excel’s MID function extracts a given number of characters starting from a given number of characters. As we want to extract text between spaces, we can just specify character numbers using the FIND function in both starting and returning characters.

The syntax of the MID function is

MID (text, start_num, num_chars)

In arguments,

♦ text; The text from which we extract characters.

♦ start_num; The first character location we start extraction.

♦ num_chars; Total number of characters to extract.

Also, the syntax of the FIND function is

FIND (find_text, within_text, [start_num])

♦ find_text; The text or character to find.

♦ within_text; The text to find within.

♦ start_num; The starting position within the text to find. By default, it’s 1, [optional].

Step 1: Type the following formula in any blank cell (i.e., C5).

=MID(C5,FIND(" ",C5)+1,FIND(" ",C5,FIND(" ",C5)+1)-FIND(" ",C5))

The formula defines C5 as text, FIND(” “,C5)+1 as start_num, and FIND(” “,C5,FIND(” “,C5)+1)-FIND(” “,C5) as num_chars for the MID function. The FIND function finds the 1st Space and passes its positions adding an extra character number.  The FIND(” “,C5,FIND(” “,C5)+1) portion returns the 2nd Space position. At last FIND(” “,C5,FIND(” “,C5)+1)-FIND(” “,C5) passes the about to num_chars to extract from the text.

MID and FIND Function-Excel Extract Text Between Two Spaces

Step 2: Hit ENTER and Drag the Fill Handle to apply the formula in other cells. In a moment you see all the First Names from the entries get extracted as depicted in the following image.

Formula insertion


🔄 Using MID and SEARCH Instead of MID and FIND Functions

Similar to the MID and FIND functions, we can use the MID and SEARCH functions to extract the First Names. The syntax of the SEARCH function is

SEARCH (find_text, within_text, [start_num])

The arguments declare the same logic as the FIND function. However, the FIND function is case sensitive and the SEARCH function isn’t. As we are dealing with spaces there is no issue with the Case Sensitivity of functions here.

🔺 Paste the below formula in any cell and the formula executes the same logic as the MID and FIND formula.

 =MID(C5, SEARCH(" ",C5) + 1, SEARCH(" ",C5,SEARCH(" ",C5)+1) - SEARCH(" ",C5) - 1)

MID and SEARCH Function-Excel Extract Text Between Two Spaces

Read More: How to Extract Text Before Character in Excel


Method 2: Fetch Text between Spaces Using SUBSTITUTE, MID, REPT Functions

The SUBSTITUTE function replaces a given character with a given character within a cell. The REPT function repeats a given character; a given number of times. And the MID function fetches a given number of characters.

Step 1: Use the following formula in cell C5.

=SUBSTITUTE(MID(SUBSTITUTE(" " & C5&REPT(" ",6)," ",REPT(",",255)),2*255,255),",","")

The portions of this above formula,

🔼 SUBSTITUTE(" " & C5&REPT(" ",6)," ",REPT(",",255)takes ” ” & C5&REPT(” “,6) as text, ” “ as old_text, and REPT(“,”,255) as new_text. The new_text is the repetition of the comma, 255 times.

🔼 MID(SUBSTITUTE(" " & C5&REPT(" ",6)," ",REPT(",",255)),2*255,255) passes ” ” & C5&REPT(” “,6),” “,REPT(“,”,255)) as text, 2*255 as start_num, and 255 as new_text. This portion fetches the 255 characters including commas.

🔼 SUBSTITUTE(MID(SUBSTITUTE(" " & C5&REPT(" ",6)," ",REPT(",",255)),2*255,255),",","")replaces inserted commas with no spaces in extracted characters.

The character numbers used in the formula are random. You can assign any number (near to the total character numbers) to the REPT function or the MID function to replicate or extract characters in or from the text.

SUBSTITUTE Function-Excel Extract Text Between Two Spaces

Step 2: Press ENTER then Drag the Fill Handle to display all the First Names appearing as shown in the picture below.

Formula insertion

Read More: How to Extract Text after a Specific Text in Excel


Method 3: Using TRIM, MID, and REPT Functions to Extract Text between Spaces

Similar to Method 2, we can use the TRIM function instead of the SUBSTITUTE function to delete all the repeated characters.

Step 1: Insert the following formula in any cell.

=TRIM(MID(SUBSTITUTE(C5, " ", REPT(" ", 99)), 2 * 99 - 98, 99))

🔼 SUBSTITUTE(C5, " ", REPT(" ", 99))declares C5 as text, ” “ as old_text, and REPT(” “,99) as new_text. The new_text is the repetition of the Space, 99 times.

🔼 MID(SUBSTITUTE(C5, " ", REPT(" ", 99)), 2 * 99 - 98, 99) offers SUBSTITUTE(C5, ” “, REPT(” “, 99)) as text, 2 * 99 – 98 as start_num, and 99 as num_chars. The MID function fetches 99 characters after the start_num characters.

🔼 TRIM(MID(SUBSTITUTE(C5, " ", REPT(" ", 99)), 2 * 99 - 98, 99))deletes all the inserted Spaces within the fetched characters. And all the numbers in the formula are random. You can use any number more or close to the total character numbers in the text to replicate the formulas.

TRIM and MID Function-Excel Extract Text Between Two Spaces

Step 2: Use the ENTER key and Fill Handle to apply the formula in other cells.

Formula result

Read More: How to Extract Text After First Space in Excel


Method 4: Split Text between Spaces Using Text to Column Feature

Excel provides the Text to Columns feature in the Data tab. Text to Columns features allow the split of text considering delimiters (i.e., Space, Comma, etc.) as separating indicators.

Step 1: Select the entire column you want to split text from. Go to the Data tab > Click on Data Tools > Select Text to Columns.

Text to columns-Excel Extract Text Between Two Spaces

Step 2: The Convert Text to Columns Wizard appears. In the Wizard,

♦ Mark Delimited as Choose the file type that best describes your data.

♦ Click on Next.

Wizard

Step 3: Convert Text to Columns Wizard- Step 2 of 3 pops up. In the Wizard,

♦ Tick Space as Delimiters. You can choose other options (i.e., Tab, Semicolon, Comma, etc.,) as delimiters depending on your data type. As the article suggests text extraction between spaces, Space is chosen as the delimiter to define separations.

♦ Click on Next.

Wizard

Step 4: Last Convert Text to Columns Wizard appears.

♦ Mark the Column data format as in any preferred option (i.e., General). You can mark Text as outcome data format.

♦ Click on Finish.

Final wizard

🔼 Return to the Worksheet and furnish the data as you need. At last, the outcome will be similar as depicted in the following picture.

Final outcome

Though it’s not the exact extraction of data, you can still use the split text as extracted text between spaces.


Similar Readings


Method 5: Inserting Desired Text Using Flash Fill

Flash Fill is a handy tool to fast-fill entries in Excel. Flash Fill mimics the previously inputted entries and fetches data from existing entries. All it needs is to provide sufficient inputs to mimic.

Steps: Type the 1st entry Under the First Name header. Go to Home > Editing > Fill > Select Flash Fill.

Flash Fill-Excel Extract Text Between Two Spaces

🔼 Instantly, all the other entries within the First Name header get inserted with First Names. Flash Fill mimics the 1st entry and fetches all the similarly positioned words from the adjacent column.

Filled


Download Excel Workbook


Conclusion

In this article, we use features such as Text to Columns, Flash Fill, and combined functions that allow Excel to extract text between two spaces. The combined functions are on point regarding the extraction of text between spaces. However, the Text to Columns and Flash Fill features need manual intervention to extract desired text from entries. Hope you find these above-described methods handy in your case. Comment, if you have further inquiries or have anything to add.


Related Readings


<< Go Back to Extract Text in Excel | String Manipulation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo