Productivity

How to Extract a Number or Text From Excel

Tina Sieber Updated 03-01-2020

Microsoft Excel is great at working with both numbers and text—but if you’re using both in the same cell, you might run into some difficulty. Fortunately, you can extract numbers or text from cells to work with your data more efficiently. We demonstrate several options, depending on the format that your data is currently in.

Advertisement

Excel Numbers Formatted as Text

This is a common situation, and—fortunately—very easy to deal with. Sometimes, cells that contain only numbers are incorrectly labeled or formatted as text, preventing Microsoft Excel from using them in operations.

You can see in the image below that the cells in column A are formatted as text, as indicated by the number format box. You might also see a green flag in the top left corner of each cell.

Numbers formatted as text in Excel.

Convert Text to Number in Excel

If you see the green flag in the top left corner, select one or more cells, click the warning sign, and select Convert to Number.

Convert text to number using the warning shown in Excel cells.

Advertisement

Otherwise, select the cells and, in the Number Format menu in the Ribbon, select the default Number option.

Convert text to number in Excel via the Ribbon menu.

If you need more granular options, right-click the highlighted cell/s and select Format Cells, which will open the respective menu. Here, you can customize the number format and add or remove decimals, add a 1,000 separator, or manage negative numbers.

Format numbers and text in Excel via the Format Cells menu.

Advertisement

Obviously, you can also use the Ribbon or Format Cells options outlined above to convert a number to text, or text to currency, time, or any other format you desire.

Apply Number Formatting With Excel’s Paste Special

For this method to work, you’ll need to enter a number (any number) into a cell; it’s important that this cell is also formatted as a number. Copy that cell. Now, select all the cells that you want to convert to the number format, go to Home > Paste > Paste Special, select Formats to paste only the formatting of the cell you copied initially, then click OK.

Paste the formatting of a cell to other cells in Excel.

This operation applies the format of the cell you copied to all selected cells, even text cells.

Advertisement

Extract Numbers or Text From Mixed Format Cells

Now we get to the hard part: getting numbers out of cells that contain multiple formats of input. If you have a number and a unit (like “7 shovels,” as we have below), you’ll run into this problem. To solve it, we’re going to look at a couple different ways to split cells into numbers and text, letting you work with each individually.

Separate Numbers From Text

If you have a lot of cells that contain a mix of numbers and text or multiples of both, separating them manually might take a monumental amount of time. To get through the process faster, you can use Microsoft Excel’s Text to Columns function.

Select the cells that you want to convert, go to Data > Text to Columns, and use the wizard to make sure the cells come out correctly. For the most part, you’ll just need to click Next and Finish, but do make sure you pick a matching delimiter; in this example, a comma.

Separate text in cells into columns in Excel.

Advertisement

If you only have one- and two-digit numbers, the Fixed Width option can be useful too, as it will only split off the first two or three characters of the cell. You can even create a number of splits that way.

Note: Cells formatted as text will not automatically emerge with a number formatting (or vice versa), meaning you might still have to convert these cells as described above.

Extract a Number or Text From a Delimited String

This method is a bit cumbersome, but works very well on small datasets. What we assume here is that a space separates the number and text, though the method also works for any other delimiter.

The main function we’ll be using here is LEFT, which returns the leftmost characters from a cell. As you can see in our dataset above, we have cells with one-, two-, and three-character numbers, so we’ll need to return the leftmost one, two, or three characters from the cells. By combining LEFT with the SEARCH function 4 Excel Lookup Functions to Search Spreadsheets Efficiently Searching a large Excel spreadsheet isn't always easy. Use lookup formulas to save time and search spreadsheets efficiently. Read More , we can return everything to the left of the space. Here’s the function:

=LEFT(A1, SEARCH(" ", A1, 1))

This will return everything to the left of the space. Using the fill handle to apply the formula to the rest of the cells, this is what we get (you can see the formula in the function bar at the top of the image):

Combine LEFT and SEARCH functions to separate text and numbers in Excel.

As you can see, we now have all the numbers isolated, so we can manipulate them. Want to isolate the text as well? We can use the RIGHT function in the same way:

=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))

This returns X characters from the right side of the cell, where x is the total length of the cell minus the number of characters to the left of the space.

Excel Separate Numbers and Text With LEFT and SEARCH Functions

Now you can also manipulate the text. Want to combine them again? Just use the CONCATENATE function with all the cells as inputs:

=CONCATENATE(E1, F1)

Obviously, this method works best if you just have numbers and units, and nothing else. If you have other cell formats, you might have to get creative with formulas to get everything to work right. If you have a giant dataset, it’ll be worth the time it takes to get the formula figured out!

Extract a Number From One End of a Continuous String

Now what if there’s no delimiter separating your number and text?

If you’re extracting the number from the left or right of the string, you can use a variation of the LEFT or RIGHT formula discussed above:

=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))

This will return all numbers from the left or right of the string.

Extract numbers from the left or right of a string of text and numbers in Excel using the LEFT or RIGHT function.

If you’re extracting the number from the right of the string, you can also use a two-step process. First, determine the location of your first digit in the string using the MIN function. Then, you can feed that information into a variation of the RIGHT formula, to split your numbers from your texts.

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
=RIGHT(A1, LEN(A1)-B1+1)

Separate a Number from the right side of a string of text and numbers in Excel.

Note: When you use these formulas, remember that you might have to adjust the column characters and cell numbers.

Extract Numbers From Both Ends of a Continuous String

With the strategies above, you should be able to extract numbers or text out of most mixed-format cells that are giving you trouble. Even if they don’t, you can probably combine them with some powerful text functions included in Microsoft Excel to get the characters you’re looking for. However, there are some much more complicated situations that call for more complicated solutions.

For example, I found a forum post where someone wanted to extract the numbers from a string like “45t*&65/”, so that he would end up with “4565.” Another poster gave the following formula as one way to do it:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
 ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

To be completely honest, I have no idea how it works. But according to the forum post, it will take the numbers out of a complicated string of numbers and other characters. The point is that, with enough time, patience, and effort, you can extract numbers and text from just about anything!

You just have to find the right resources Need Help with Excel Formulas? 7 Resources to Consult Excel is the spreadsheet gold standard. If you are required to use Excel and yet have to familiarize yourself with it, these resources will quickly introduce you to the basics and more. Read More .

Related topics: Microsoft Excel, Spreadsheet.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

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

  1. Marcia
    January 30, 2020 at 8:22 am

    very hand the one that extracts numbers only from the mixed cell.
    =LEFT(A1, SEARCH(" ", A1, 1))
    Thank you :)

  2. Jason Weiss
    January 8, 2020 at 4:16 am

    I have a list and each entry has a separator of : and then either 1 or 2 digits followed by a . and then another digit.
    All I want to do is show the text. I've tried different ways, but none work.
    Here's a small sample with the formula variation used and the output.

    alpha:99.2 - =LEFT(M2, SEARCH(":", M2, 1)) - alpha:
    nimue:99.1 - =RIGHT(M3, SEARCH(":", M3, 1)) - e:99.1
    athos:59.2 - =RIGHT(M4, LEN(M4)-SEARCH(":", M4, 1)) - 59.2
    mechamary:5.0 - =LEFT(M5, LEN(M5)-SEARCH(":", M5, 1)) - mec

    All I want is
    alpha
    nimue
    athos
    mechamary

  3. Wengy
    September 21, 2017 at 1:35 pm

    Hello, I need a formula that will take this data and get rid of all the text while multiplying the 2 numbers so it equals 3,000 (for example).

    Black & Red Berries 12x250g

    Can anyone help me with this? There's about 700 rows in my Excel sheet.

  4. Andy
    August 6, 2017 at 11:18 pm

    In the section left/right & search, why use =CONCATENATE(E1, F1) ?
    Surely =E1&F1 does the same thing?

  5. Chris
    February 1, 2017 at 4:47 am

    Thanks for posting that last solution. That was the only one that would work for my application. .. I don't have a clue how it works either, but it got the job done!

    • Dann Albright
      February 6, 2017 at 6:35 pm

      Sometimes you just have to copy and paste a solution without knowing how it works. :-)

  6. Bhushan
    December 2, 2016 at 10:02 am

    THANK YOU SO MUCH
    =LEFT(D1307, SEARCH(" ", D1307, 1)) THIS FORMULA IS BEST

  7. JIGNESH PATEL
    November 23, 2016 at 12:25 pm

    THANK YOU SO MUCH
    =LEFT(D1307, SEARCH(" ", D1307, 1)) THIS FORMULA IS BEST

  8. yasir ali
    October 8, 2016 at 12:34 pm

    Dear I want to extract different value from same cell
    like
    yasir ali k15245M05658
    jhaon son K2Moidoio
    anwar K232Mhjhajh. I need valve between k and M from all 3 cell in there next cell

  9. Dane
    July 11, 2016 at 11:26 pm

    I have two sets of data:
    https://ubercab.zendesk.com/agent/tickets/116831900
    i need to get the characters/number after the last "/"

    please help.

    Thanks!

  10. Stella
    July 11, 2016 at 7:25 pm

    Hi I am trying to extract from a cell a persons name and separate into 1 column row by row vertically the persons name. for example John bob smith in A1 into cell b1 J, b2 o, b3 h, b4 n, b5 space, b6 b, b7 0, b8 b, b9 space, b10 s, b11 m, b12 I, b12 t, b13 h. I want to be able to type in cell a1 the name and that whenever you change the formula will automatically recalculate the letters accordingly. Is this possible I have tired the text/columns, len, mid, left, right, and I still can't seem to get the name in cell a1 to vertically go into column b only vertically.

  11. Anna LN
    July 7, 2016 at 10:59 am

    Hi Tim,

    I was wondering if you might be able to help I want to be able to extract A0-A5 wherever they are in a cell and copy them into a new cell, examples:

    Column A
    R1 2 QTY - Online A0 Full Colour Graphics Special Material
    R2 2 QTY - Online A0 Drymounted Polyboard 5mm
    R3 1008 QTY - Online SRA3 Colour Laser Copy
    R4 7 QTY - Online Perfect Bound
    R5 342 QTY - Online A1 Colorwave Plain Paper
    R6 638 QTY - Online A4 Colour Laser Copy
    R7 1 QTY - Bench Work Hourly rate (Greenland)
    R8 2 QTY - Inkjet Laminated Covers
    R9 150 QTY - Online A3 Colour laser copy 160gsm

    Column B
    R1 A0
    R2 A0
    R3 A3
    R4 0
    R5 A1
    R6 A4
    R7 0
    R8 0
    R9 A3

    What formula could I use so as to not need to do this manually?

  12. Raul
    May 28, 2016 at 9:30 pm

    I have this number in Cell A1

    02/01/2016 

    and want to extract the fifth character to another cell (in the example will be "1") so I can set in another cell the month value (in the example would be January)

    Any help please ?

    • Dann Albright
      May 30, 2016 at 2:01 pm

      You'll probably want to use the MID function. I think you'd use MID(A1, 5, 1), where A1 is the cell you want to take the month from. If you want to convert the number to the written month, you'll need an IF statement (IF A1=1, "January", ELSEIF A1=2, "February"...), or something similar to that.

  13. Raajeish
    March 25, 2016 at 8:18 am

    I have data like

    1209002815P101005093

    want like

    120900/28/15/P1/01005093

    i.e. want / between Nos.

    any formula, please help

  14. Adrie
    March 23, 2016 at 6:06 am

    Good day

    I would like to extract the first set of numbers from a list. ie (122,90,84,118.4,128.9)
    Any ideas on what formula I can use?

    COIL112X2.5
    COIL90X2.5
    COIL84X2.0
    COIL118.4X1.8
    COIL128.9X2.0

    • Tim SW
      June 10, 2016 at 10:10 pm

      Hi Adrie,

      This formula should be perfect for you. In this example, let's assume A1 is where the data is that you want to extract from.

      =MID(A1,5,3)

      You can copy and paste this formula to just grab those digits since the beginning of the set is 4 characters(COIL).

      Tim SW

  15. richard
    March 16, 2016 at 3:53 pm

    I have a list of data
    1603CFR221
    1603BSC410
    1512IT402
    1212BA004
    1509SM001

    The sequence is 4 numbers followed by a string of 2 or 3 letters.

    I want to only extract the letters.

    What formula would you suggest?

    Thanks, Richard

    • Mars
      March 23, 2016 at 7:09 pm

      =MID(A1,5,LEN(A1)-7)

      Assuming the data is in cells A1:A5

  16. Yadhu nandan
    February 25, 2016 at 12:48 pm

    Hi

    I want to know how t separate the numerical and alphabets.

    Ex is - 4552dfsdg6652sdfsdfd5654

    I want 4552 6652 5654 in different cells

    • Tim SW
      June 10, 2016 at 10:00 pm

      Assuming that 4552dfsdg6652sdfsdfd5654 is in A1 and you want these numbers extracted into 3 different cells. 4452 in say cell B1 and 6652 in C1 and 5654 in D1 you'd use these.

      B1:

      =MID(A1,1,4)

      C1:
      =MID(A1,10,4)

      D1:
      =MID(A1,21,4)

      Let me know if you need more help.

      Tim K SW

  17. colin
    February 25, 2016 at 3:09 am

    hi, if i want to pick 12 numeric digits from the following data:

    s1234567892345
    791213135422
    901213-12-5421
    a12345

    what formula i can use? Based on the examples above, i need 2 and 3rd numbers.

    Many thanks.

  18. Kim
    February 18, 2016 at 8:36 am

    What if I want to know if a certain word exists in 4 different cells all at once.

    E.g

    Cell 1: 1234ok4567
    Cell 2: 12345678
    Cell 3: ok98765
    Cell 4: ok

    Cell 5: (a formula wherein it can let me know that the word "ok" can be found in those 4 cells, or either one of those cells)

    I use this formula to extract a word in one cell: IF(ISERR(FIND("texttofind","cell","startnum"),0,"texttofind")

    But now I need to find the same word on 4 different cells, i tried extending the formula above but it doesn't seem to work well, it misses other cells.

  19. daryl
    January 12, 2016 at 10:18 am

    Hi, I just want to ask how to separate very unformatted entry like :

    Rp. 487.500 (Nett 50% OFF)
    Rp 256.500 Nett 40% OFF
    Rp99.000
    Rp 51.000/orang Nett (50% Off)

    I just need the number i.e. 487500, 256500,99000 and 51000

    can anyone help me ?

    thanks
    daryl

    • Gerald
      January 27, 2016 at 8:31 am

      =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
      ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

      That formula works

  20. Vineet
    December 10, 2015 at 3:55 pm

    I would give credit to author for being honest about the formula in the end.
    Appreciated that.

    And yea, good article. :)

    • Dann Albright
      December 11, 2015 at 5:54 pm

      Thanks a lot, Vineet! I really appreciate that. I'd definitely rather post something that might be useful and say I don't totally understand it than not post it and leave people to try to find it on their own.

  21. Bob Roberts
    December 10, 2015 at 4:07 am

    I'm a big fan of custom functions for this type of thing. for the example where they want to extract just the numbers from a mixed string you can loop through the data and only return the values within the ASCII table... But this isn't an Excel forum so I'l skip the example.

    • Dann Albright
      December 11, 2015 at 5:56 pm

      Interesting; that sounds like a good way to go about it. I'm not totally sure how you'd go about doing that, but it does sound quite effective. As far as I can tell, there's a solution to just about everything in Excel if you know enough functions!

  22. Ivy
    December 9, 2015 at 7:32 am

    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
    ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10) is a cumbersome formula to do this. You seriously don't know how it works? How can you write an article instructing people yet don't know how excel functions work...?

    • Notme
      December 9, 2015 at 1:02 pm

      This is a basic based article and found it very informative. If I want a bit more I'll buy a book on the subject. This defo got me thinking.

      • Dann Albright
        December 9, 2015 at 6:20 pm

        Glad you liked the article! There are plenty of great books out there that will help you become an Excel master, but if you ever have a specific question, let us know! Leave a comment on one of my articles somewhere and I'll get back to you with an answer, or maybe even write an entire article about it. We always want to know what kinds of problems people are trying to solve.

    • Dann Albright
      December 9, 2015 at 6:19 pm

      It is indeed a cumbersome formula, but I haven't come across a better one. If you have an alternative formula that's more efficient, please share it with us!

      Also, I didn't say that I "don't know how [E]xcel functions work," I said that I don't know exactly how THIS one works. As you can see, it's a rather complicated one that uses many different functions and combines them in complex ways. Just because I haven't taken the time to walk through every single step (which would take a long time) doesn't mean that it's not useful—I know that some people reading this article will find that it solves their problem, and that's what's important to me, not understanding every single step it goes through.

    • Mike
      December 10, 2015 at 3:35 am

      This article is intended for novice Excel users - otherwise it wouldn't have started with general cell formatting. I agree with Dann that the formula is cumbersome and not worth spending time to decipher if the only point of referencing it is to show that there are superusers out there willing to help novice users solve complex problems.

  23. Anonymous
    December 8, 2015 at 10:17 pm

    This is awesome! Great article! I've had need of this in the past, and had to kludge around to get by. I hope this same process works in Google Sheets too.

    • Dann Albright
      December 9, 2015 at 6:21 pm

      I'm glad you found this information useful! I haven't checked to see if this works in Google Sheets . . . a lot of the formulas are the same, but some are different enough that you have to take a different approach. Maybe I'll give it a shot and report back!