• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extract Text from a Cell using MID Formulas

You are here: Home / Excel Formulas / Extract Text from a Cell using MID Formulas
Extract Text from a Cell using MID Formulas
September 4, 2013 by Mynda Treacy

Last week we looked at how to use Excel’s Text to Columns tool to extract the domain name from a URL.

Today we’re going to look at how we can achieve the same result using a formula like this one displayed in the formula bar below:

Extract domain from URL example using Excel Formulas

Don’t be put off by its length. First we’ll step through how this formula works and at the end I’ll show you a system for building formulas like this in a few easy steps.

Note; we’ll use the Ikea URL in cell A4 throughout this example.

Let’s start with the functions used in the formula above:

SEARCH or FIND

SEARCH and FIND are almost identical; the only difference being that FIND is case sensitive, whereas SEARCH is not. I have used SEARCH but if you need a case sensitive match then use FIND.

FIND returns the starting position of one text string within another text string and IS case sensitive. The syntax is:

FIND(find_text, within_text, [start_num])

SEARCH returns the starting position of one text string within another text string and is NOT case sensitive. The syntax is:

SEARCH(find_text, within_text, [start_num])

For example the first ‘w’ in the Ikea URL is in position number 8:

http://www.ikea.com/au/en/preindex.html?region=au-east

In this example I've used the SEARCH function to return the arguments for the MID function.

MID

MID returns the characters from the middle of a text string, given a starting position and length. The syntax is:

MID(text, start_num, num_characters)

The start_num we want is 8 i.e. the position of the first ‘w’ in the URL, and the num_characters for the domain is 12 i.e. ‘www.ikea.com’ is 12 characters long.

So we now know that our MID formula should be:

=MID(A4,8,12)

However since each domain is a different length we want to automate the tasks of finding the start_num and num_characters.

To do this we’ll use the SEARCH function to locate a character or characters that are on either side of the domain name and are common to each URL, (known as delimiters).

Identifying Delimiters

First let’s choose what delimiters are common to each URL.

identify delimiters

We can see that every URL has the text string :// before the domain name.

The location of this text string in the Ikea URL is 5 i.e. the colon in :// is the 5th character in the URL. We then add 3 to this (:// is 3 characters) to give the position of the first ‘w’ which is 8. This will be our start_num argument for the MID function.

We can also see that at the end of each domain is another forward slash /. Locating this will help us calculate the num_characters argument for the MID function. In the Ikea URL the first single / is in position 20.

But we don’t want to return 20 characters as this will be too many, so we need to subtract the 8 i.e. the number of characters up to the first ‘w’, from 20 to give us the domain length of 12.

Identify delimiters for SEARCH Formula

Using SEARCH to Locate Characters

Remember the syntax for the SEARCH function is:

SEARCH(find_text, within_text, [start_num])
Note: start_num is optional for the SEARCH function. It will start at the beginning if omitted.

Here is the formula for the Ikea URL in cell A4, colour coded so we can follow how it works:

=MID(A4,SEARCH("://",A4)+3,SEARCH("/",A4,SEARCH("://",A4)+3)-SEARCH("://",A4)-3)

We'll look at the first SEARCH formula which gives us the MID start_num argument (in this formula we aren’t using the SEARCH function's ‘start_num’ argument):

SEARCH("://",A4)+3 

In English it reads; search cell A4 and tell me the start number for text string :// then add 3 (remember, we add 3 on the end because :// is 3 characters long and we want to return the text starting after the ://) which is:

=8

The num_characters, or length of the characters we want returned is determined by the location of the first forward slash “/” after the domain, minus the number of characters up to the end of ://.

Again we can use the SEARCH function to find the “/” (note: in this formula we are using the SEARCH Function's ‘start_num’ argument to tell the it to start searching after the ://):

SEARCH("/",A4,SEARCH("://",A4)+3)-SEARCH("://",A4)-3

=20-SEARCH("://",A4)-3

=20-8

=12

In English: search for “/”, in cell A4, start your search after the “://” characters +3, minus the preceding number of characters up to the end of :// to get the length of the domain.

SEARCH Formulas

This formula will work for each URL in the list, including the URL that starts with ‘https’ as some do.

Tip: if some of your URL’s simply start with www and don’t have the http:// at the front you can use this formula:

=IF(ISNUMBER(SEARCH("://",A4)),MID(A4,SEARCH("://",A4)+3,SEARCH("/",A4,SEARCH("://",A4)+3)-SEARCH("://",A4)-3),LEFT(A4,SEARCH("/",A4)-1))

The above formula checks if the :// is present in the URL and if it is is uses the original formula we looked at, however if it isn’t it returns the characters on the LEFT up to the / -1, assuming the URL is in this format:

www.ikea.com/au/en/preindex.html?region=au-east

System for Building MID Formulas

One of the challenges with building complex MID formulas is getting all the nesting right. Trust me, once you start to nest more than two SEARCH functions you lose track of where you’re up to and you might get that head spinning feeling.

This is why I like to use helper columns to locate the characters using one formula at a time. Once I’ve got all the arguments for my MID function using individual formulas I can nest them into one big formula and get rid of my helper columns.

Here is an example where I want to extract text from the middle of the string in column A in the format displayed in column F (note how all strings in column A are not the same length):

Helper columns for working out MID formula

You can see from the image above that I have used 4 helper columns (B to E) before coming to my Final Formula in column F. They are:

Column B: The SEARCH function locates the position of the first space before the text I want to extract.

Column C: The SEARCH function locates the position of the hyphen that I need to exclude from my new text string.

Column D: Uses the result in columns B and C to extract the alpha component of my new text string.

Column E: Uses the result in column C to extract the numeric component of my text string.

Interim Formula

Before building my final formula I first write an Interim Formula in column F that references the results from the SEARCH formulas in columns B and C:

=MID(A7,B7+1,C7-B7-1)&MID(A7,C7+1,2)

I check it’s returning the correct result and then replace the cell references to columns B and C with the actual formulas in those helper columns like so:

=MID(A7,SEARCH(" ",A7)+1,SEARCH("-",A7)-SEARCH(" ",A7)-1)&MID(A7,SEARCH("-",A7)+1,2)

Now I can delete the helper columns and I’m done.

Key Steps

To summarise I take the following steps:

  1. Identify the delimiters I can use in my SEARCH or FIND formulas to locate the positions in the text string that are common to all of the strings in column A. In this example it was the first space and the hyphen.
  2. Create helper columns to house my SEARCH or FIND formulas that locate the delimiters I need to use.
  3. Build an Interim Formula in column F that references the helper columns. Technically the formulas in columns D and E are interim formulas too.
  4. Edit the Interim Formula in column F and replace the references to the helper columns with the actual formulas in those helper cells to get your Final Formula.
  5. Delete the helper columns.

Don’t forget you could also use Text to Columns to achieve the same results much faster than writing a complex nested MID formula.

Extract Text from a Cell using MID Formulas

More Text Formulas Posts

Excel TEXT Function – handy but limited…or is it?

Excel TEXT Function – handy but limited…or is it?

The Excel TEXT Function converts numbers to text in the format you specify. It's hand for joining numbers and text together in custom chart labels etc.
Excel Test if a Range Contains Text, Numbers or is Empty

Excel Test if a Range Contains Text, Numbers or is Empty

Excel CLEAN Formula

Excel CLEAN Formula

The Excel CLEAN Function can help you to remove unwanted characters that are often imported when you copy data from web pages, but it has some limitations
Excel SUBSTITUTE Formula

Excel SUBSTITUTE Formula

The Excel SUBSTITUTE function replaces new text for old in a text string. It's an alternative to using Find and Replace by retaining the original data.
Excel Factor Entry 3 Re-format Data Using Formulas

Excel Factor Entry 3 Re-format Data Using Formulas

Use Excel's Text functions to re-format data imported from external sources
Excel SEARCH and You Will FIND

Excel SEARCH and You Will FIND

Excel SUBSTITUTE Function Trick

Excel SUBSTITUTE Function Trick

Excel TRIM Function Removes Spaces From Text

Excel TRIM Function Removes Spaces From Text

Excel TRIM function removes spaces from the start and end of text, while leaving spaces between words untouched.
Excel UPPER LOWER and PROPER Functions

Excel UPPER LOWER and PROPER Functions

Microsoft Excel’s T Function

Microsoft Excel’s T Function

The Excel T function checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it isn’t.

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: text formulas
Previous Post:Extracting Text Strings Using Excel’s Text to ColumnsExtracting Text Strings Using Excel’s Text to Columns
Next Post:Excel Formula – List Missing Numbers in a SequenceExcel Formula - List Missing Numbers in a Sequence

Reader Interactions

Comments

  1. samsung uae

    November 24, 2020 at 2:59 pm

    Good! This post is creative, you’ll find a lot of new idea, it gives me inspiration. I believe I will also inspired by you and feel about extra new ideas. thanks.

    Reply
  2. Stacey Anderson

    April 20, 2020 at 3:47 am

    Hi there, is there anyway of sorting a column in ascending order if the values in that column look like this (special characters, /’s and -‘s in some cases more than 1 forward slash. I need to present them (and associated data) in ascending order so for example, 178/1-4 would be first followed by 5-34 and so on…

    178/148
    178/149
    178/166
    178/183/55
    178/183/63
    178/183/47-48
    178/183/PART
    178/216/1-21
    178/216/22-42
    178/217
    178/224
    178/1-4
    178/105-112
    178/113-127
    178/128-134
    178/135-139
    178/140-147
    178/150-162
    178/163-165
    178/167-180
    178/181-182
    178/184-204
    178/205-215
    178/218-222
    178/34-43
    178/44-57
    178/5-34
    178/58-95
    178/96-104

    Reply
    • Mynda Treacy

      April 20, 2020 at 2:11 pm

      Hi Stacy,

      You would have to split the numbers into separate columns first, then sort by the first column, then the second and so on. You could use Text to Columns for this. Be sure to split by the hyphen first, then the forward slash, otherwise text like 1-4 turns into a date.

      Mynda

      Reply
  3. malleswari

    May 8, 2017 at 9:03 pm

    How to extract list of characters at a time , Please guide me

    Reply
    • Philip Treacy

      May 9, 2017 at 9:18 pm

      Not quite sure what you mean. Do you mean extract 1 letter at a time from a string? Effectively splitting the string into it’s component characters?

      Phil

      Reply
  4. Cathy

    March 24, 2014 at 4:51 pm

    Does this scenario rely on looking for delimiters? I would like to search a cell containing an item name and return yes if the item name contains the particular word. That word could be anywhere in the item name as all item names are different. There are no delimiters. Thanks in advance!

    Reply
    • Mynda Treacy

      March 24, 2014 at 7:34 pm

      Hi Cathy,

      If you just want to test if a word exists in a text string you can use the SEARCH (not case sensitive), or FIND (case sensitive) functions. These will return the position (number of the first character) in the text string where your word starts, so all you then need to do is test for a number e.g.:

      =IF(ISNUMBER(SEARCH(“word”,A2)),”yes”,””no”)

      More on SEARCH and FIND here.

      Kind regards,

      Mynda.

      Reply
  5. Bryan

    September 4, 2013 at 10:53 pm

    I use the helper column approach a LOT. In fact, I rarely bother to join it into one cell, because it speeds up calculation time (in your example, the single-cell formula does 4 searches, whereas the multi-cell formula does 2).

    The helper column approach doesn’t have to be limited to MID-type functions either. Any time I have a complicated function I put each part in a different cell and join them together once all the parts are working. It just makes troubleshooting so much easier (and you are less likely to have to get out of a cell mid-formula, causing you to lose the whole thing).

    Reply
    • Mynda Treacy

      September 4, 2013 at 11:01 pm

      Too true, Bryan. Thanks for sharing.

      I meant to mention your point about not limiting this technique to just this scenario but it slipped my mind, so thanks 🙂

      Cheers,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.