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

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.

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.

Download Excel Workbook

5 Easy Ways to Extract Text between Two Spaces in Excel

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

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_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).

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 1^st Space and passes its positions adding an extra character number.  The FIND(” “,C5,FIND(” “,C5)+1) portion returns the 2^nd Space position. At last FIND(” “,C5,FIND(” “,C5)+1)-FIND(” “,C5) passes the about to num_chars to extract from the text.

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.

🔄 Using MID and SEARCH Instead of MID and FIND Functions

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

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.

Read More: How to Extract Text from a Cell in Excel (5 ways)

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.

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.

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

Read More: How to Extract Text After First Space in Excel (3 Methods)

Similar Readings

• Extract Text Before Character in Excel (4 Quick Ways)
• How to Extract Text After Last Space in Excel (5 Ways)
• Extract Text After a Character in Excel (6 Ways)

Method 3: Using TRIM, MID, 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.

🔼 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.

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

Read More: How to Extract Text after Second Space in Excel (6 Methods)

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 allows 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.

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.

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.

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.

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

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

Read More: How to Extract Text Between Two Commas in Excel (4 Easy Approaches)

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 1^stentry Under the First Name header. Go to Home > Editing > Fill > Select Flash Fill.

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

Read More: How to Extract Text Between Two Characters in Excel (4 Methods)

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 Articles

• How to Extract Text after Second Comma in Excel (6 Methods)
• Extract Certain Text from a Cell in Excel VBA (5 Examples)
• How to Extract Text after a Specific Text in Excel (10 Ways)