**Affiliate Disclosure:** By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.

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.

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

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

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

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.

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

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

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

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

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.

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.

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)

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

Explore more about: Microsoft Excel, Spreadsheet.

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

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.

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

Surely =E1&F1 does the same thing?

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!

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

THANK YOU SO MUCH

=LEFT(D1307, SEARCH(" ", D1307, 1)) THIS FORMULA IS BEST

THANK YOU SO MUCH

=LEFT(D1307, SEARCH(" ", D1307, 1)) THIS FORMULA IS BEST

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

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!

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.

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?

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 ?

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.

I have data like

1209002815P101005093

want like

120900/28/15/P1/01005093

i.e. want / between Nos.

any formula, please help

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

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

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

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

Assuming the data is in cells A1:A5

Hi

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

Ex is - 4552dfsdg6652sdfsdfd5654

I want 4552 6652 5654 in different cells

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

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.

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.

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

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

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

=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

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

Appreciated that.

And yea, good article. :)

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.

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.

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!

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

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.

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.

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.

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.

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.

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!