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

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.

Similar Readings

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 1^st entry and fetches all the similarly positioned words from the adjacent column.

Download the Excel Workbook

Extract Text between Two Spaces.xlsx