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)`

**Read More: How to Extract Text Before Character in Excel**

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

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

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

**How to Extract Text after Second Space in Excel****How to Extract Text After Last Space in Excel****How to Extract Text Between Two Commas in Excel**

**Method 5 – Inserting Desired Text Using Flash Fill**

**Steps:**

- Type the first
*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**

## Related Readings

**How to Extract Text after Second Comma in Excel**

**How to Extract Text Between Two Characters in Excel****How to Extract Certain Text from a Cell in Excel VBA****How to Extract Text After a Character in Excel**

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