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

For illustration, we will extract First Names from the sample dataset shown.

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 where we start extraction.

â™¦ num_chars; Total number of characters to extract.

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: Add 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.

Step 2: Hit ENTER and drag the Fill Handle to apply the formula in other cells. 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 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 formula below 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)`

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

Step 1: Use the following formula in cell C5.

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

Formula breakdown:

`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 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 a Specific Text in Excel

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

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.

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

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

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:Â Convert Text to Columns Wizard Step 3 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 format the data as desired. The outcome will be similar as depicted in the following image.

Method 5 – Inserting Desired Text Using Flash Fill

Steps:

• Type the firstÂ entry Under the First Name header.
• Go to Home > Editing > Fill > Select Flash Fill.

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.

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF