Office For Mac

Find it fast! Pull data from a string

Do you have a list of data that’s in a format you
can’t use?

Do you need to “pull out” specific pieces of a
longer string?

Don’t despair! You can use simple Excel functions to
break apart strings and quickly find only the data you need.

Meet RIGHT, LEFT, and MID!

My sister Sandy recently bought a gift basket business. With the purchase came inventory spreadsheets that contained some important but unfriendly data that looked like this:

The previous owner used one column to hold a 3-digit Manufacturer’s code (assigned by him), followed by a standard 5-digit product item code, followed by a 5-digit list price:

Sandy called me looking for a way to extract these unique pieces of data without having to do it all by hand—and I showed her how she could use Excel’s MID, LEFT, and RIGHT string functions to complete this task—fast!

To get ready, I set up her spreadsheet like this—a new column heading for Manufacturer ID, one for Item Number, and one for List Price:

Then, to pull out the first 3 characters for the Manufacturer ID, in cell B2 I typed the following formula:

=LEFT(A2,3)

I reminded her that I have to press RETURN to complete any formula.

The LEFT function pulls the left-most characters from a string. A2 is the cell that contains the original data, and 3 is the number of characters that I wanted the LEFT function to grab. And as you can see, it did just that and got Manufacturer #770.

Next, I used the MID function in cell C2 to extract the middle characters from the string that contain the Item Number, like this:

=MID(A2,4,5)

A2 is the cell that contains the data, 4 is the starting position for the MID function to start getting the Item Number characters, and 5 is the number of characters that I wanted the MID function to grab.

Finally, I pulled out the right-most 5 characters of the string, which represent the List Price:

=RIGHT(A2,5)

A2 is the location of the data, and 5 is the number of characters that I wanted the RIGHT function to grab.

Now that the three formulas were in place, I selected the cells B2 – D2:

Then, I grabbed the Fill Handle and copied the formula results to the rest of the rows in the columns:

Super easy! Now Sandy can easily sort by any piece of data to analyze and manage her inventory.

To find more great tips about how Excel for Mac can help you find things fast, take a look at the Excel 2011 Function References.

- Tierney, Office for Mac writer

October 19th, 2011
Bookmark and Share
4 Comments
— @
  1. Chris says:

    How can I remove all the text before the “@” in a list of email addresses?

    • Office for Mac Team says:

      Hi Chris. I don’t know of a way to do this with an Excel function, but here’s a way a co-worker showed me in Word.
      Copy the addresses, then paste as text in Word. Then select the addresses, and click Table > Convert > Convert Text to Table.
      In the dialog, use 2 columns, and under “Separate text at,” click Other and type the @ symbol. Hope that works for you.

  2. Chris says:

    Assumes you have email addresses already in Excel and an email address in A1.

    =MID(A1,FIND(“@”,A1,1),LEN(A1)-FIND(“@”,A1,1)+1)

    p.s. I am not the original user who asked the question.

  3. Thomas Kokholm says:

    hi Chris,

    you need to use four functions in Excel, Len, Find, Left and Right.

    Asume that the string containing the @ is in A1, then you can build your formula get the string after the @:

    =RIGHT(A1;LEN(A1)-FIND(“@”;A1))

    Good luck onward..
    Thomas