Pinterest Stumbleupon Whatsapp
Ads by Google

Excel is great at working with both numbers and text Saving Time with Text Operations in Excel Saving Time with Text Operations in Excel Excel can do magic with numbers and it can handle characters equally well. This manual demonstrates how to analyze, convert, replace, and edit text within spreadsheets. These basics will allow you to perform complex transformations. Read More —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.

Numbers Stored as Text

This is a common situation, and—fortunately—very easy to deal with. Sometimes cells that contain only numbers are incorrectly labeled as text, preventing Excel from using them in operations. You can see in the image below that the cells in column A are stored as text, as indicated by the green flag on each cell as well as the format box, both of which are highlighted below.

text-format

Convert to Number

To solve the problem, just click into a cell, click the warning box, and select “Convert to number.” That’s all there is to it! This can be done on multiple cells by selecting them all and clicking on the warning box, but this likely isn’t as efficient as the following method.

convert-to-number

Text to Columns

If you have a lot of cells that need to be fixed, using this method might take a monumental amount of time. To get through the process faster, you can use Excel’s Text to Columns How To Convert Delimited Text Files To Excel Spreadsheets How To Convert Delimited Text Files To Excel Spreadsheets Read More function. Select the numbers that you want to convert, go to Data > Text to Columns, and use the wizard to make sure the numbers come out correctly (for the most part, you’ll just need to click Next and Finish without messing with any of the settings).

Ads by Google

text-to-columns

The text will be converted to numbers and you’re set to go. Note that this only works on one column at a time, so if you have a lot of columns, you might want to go with this final method.

Paste Special

For this method to work, you’ll need to enter the number 1 in a cell (it’s important that this is in number format). Select that cell and copy it. Now, select all of the numbers that you want to convert to number format, and hit Edit > Paste Special. Select “Multiply” from the Operation section and hit OK.

paste-multiply

All of the numbers that you selected will be converted to the General format, which recognizes numbers. Any text cells will also be converted to the General format, which shouldn’t cause any problems.

Extracting 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. The first method is a bit cumbersome, but works very well on small datasets.

mixed-format-cells

LEFT / RIGHT & SEARCH

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 Find Anything in Your Excel Spreadsheet with Lookup Functions Find Anything in Your Excel Spreadsheet with Lookup Functions In a giant Excel spreadsheet, CTRL+F will only get you so far. Be clever and let formulas do the hard work. Lookup formulas save time and are easy to apply. 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 How To Save Time In Excel By Using The Fill Handle How To Save Time In Excel By Using The Fill Handle Read More  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):

left-function-number-extraction

As you can see, we now have all of the numbers isolated, so they can be manipulated. 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.

right-function-word-extraction

Now the text can be manipulated as well. Want to combine them again? Just use the CONCATENATE function with all of 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!

Text to Columns

The Text to Columns function is useful for numbers-only columns, but it can also make life easier if you have mixed-format cells. Select the column you want to work with and hit Data > Text to Columns. You can then use the wizard to select a delimiter (a space is usually the best one) and have your column split the way you want it.

text-to-columns-mixed-format

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 create a number of splits if you’d like, but I’ll save a full explanation of Fixed Width splitting for another article).

If your dataset contains a lot of columns, and you’d rather not use Text to Columns for each of them, you can easily get the same effect using a quick export and import. First, export your spreadsheet as a comma-separated value (CSV) file. Click File > Save As… and save your file as a CSV.

save-csv

Now, open a new spreadsheet and click File > Import… Select your CSV file and use the import wizard to get your data split into two columns (you’ll use the exact same steps as you did with the Text to Columns wizard). By selecting “Space” in the Delimiters section, you’ll tell Excel to split the data wherever it finds a space, which will isolate the numbers and text.

Click Finish when you’re done, and you’ll get a new spreadsheet that has your columns split into two. Of course, if you have more than one space in a cell, you’ll end up with more than two columns, as you can see here:

imported-csv-excel

Unfortunately, there isn’t a good solution for this using this method; you’ll just have to concatenate the cells back together.

More Complicated Situations

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 of the powerful text functions included in 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 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 .

What Strategy Do You Use?

Now that you have a better idea of how to get numbers and letters out of mixed-format cells, we want to know what other strategies you’ve used to do the same thing. Post your formulas, processes, or anything else that might be helpful in the comments so we can all learn from them!

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

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

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

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

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

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

    I have two sets of data:
    https://ubercab.zendesk.com/agent/tickets/116831900
    https://bliss.uberinternal.com/contacts/b26530d7-2636-4008-a90b-a8e1f656ed0f

    i need to get the characters/number after the last "/"

    please help.

    Thanks!

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

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

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

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

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

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

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

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

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

  14. Tea Ruins
    February 16, 2016 at 9:09 pm

    Maybe, you can use this UDF for solving this issue..

    http://excel.mcuma.com/doku.php/functions/extract_number_from_text

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

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

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

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

  19. Kelsey Tidwell
    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!

Leave a Reply

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