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

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

### 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 Microsoft Excel’s Text to Columns
How to Convert Delimited Text Files to Excel Spreadsheets
How to Convert Delimited Text Files to Excel Spreadsheets
Use Microsoft Excel to import data from a text file into a worksheet. The Text Import Wizard helps you import data from delimited text and organize it neatly.
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).

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.

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.

### 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 Mastering the Fill Handle is the first step to becoming an Excel wizard. 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):

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.

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.

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.

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

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

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

Explore more about: Microsoft Excel, Spreadsheet.

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!