Pinterest Stumbleupon Whatsapp
Ads by Google

Many people struggle with extracting information from complex cells in Microsoft Excel (CA/UK). The many comments and questions in response to my article on How to Extract a Number or Text from Excel with this Function How to Extract a Number or Text from Excel with this Function How to Extract a Number or Text from Excel with this Function Mixing numbers and text in an Excel spreadsheet can introduce challenges. We'll show you how to change the formatting of your cells and separate numbers from text. Read More  proves it. Apparently, it’s not always clear how to isolate the desired data from an Excel sheet.

We’ve chosen a few of the questions from that article to walk through here, so you can see how the solutions work. Learning Excel quickly 8 Tips for How to Learn Excel Quickly 8 Tips for How to Learn Excel Quickly Not as comfortable with Excel as you would like? Start with simple tips for adding formulas and managing data. Follow this guide, and you'll be up to speed in no time. Read More isn’t easy, but using real-world problems like these helps a lot.

1. Extraction Using a Separator

Reader Adrie asked the following question:

I would like to extract the first set of numbers from a list, i.e. (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

The fact that the numbers to be extracted are different lengths makes this a bit more complicated than just using the MID function, but by combining a few different functions, we can get rid of the letters on the left as well as the “X” and the numbers on the right, leaving only the desired numbers in a new cell.

Here’s the formula we’ll use to solve this problem:

=VALUE(LEFT((RIGHT(A1,(LEN(A1)-4))),FIND("X",A1)-5))

Let’s start in the middle and work our way out to see how it works. First, we’ll start with the FIND function. In this case, we’re using FIND(“X”,A1). This function looks through the text in cell A1 for the letter X. When it finds an X, it returns the position that it’s in. For the first entry, COIL112X2.5, for example, it returns 8. For the second entry, it returns 7.
Next, let’s look at the LEN function. This simply returns the length of the string in the cell minus 4. Combining that with the RIGHT function, we get the string from the cell minus the first four characters, which removes “COIL” from the beginning of every cell. The formula for this part looks like this: RIGHT(A1,(LEN(A1)-4)).

Ads by Google

excel-value-function

The next level out is the LEFT function. Now that we’ve determined the position of the X with the FIND function and gotten rid of “COIL” with the RIGHT function, the LEFT function returns what’s left. Let’s break this down a little further. Here’s what happens when we simplify those two arguments:

=LEFT("112X2.5", (8-5))

The LEFT function returns the three leftmost characters of the string (the “-5” at the end of the argument ensures that the right number of characters are eliminated by accounting for the first four characters in the string).

Finally, the VALUE function ensures that the number returned is formatted as a number, instead of as text. This example isn’t as fun as building a working game of Tetris in Excel 7 Fun & Weird Things You Can Create With Microsoft Excel 7 Fun & Weird Things You Can Create With Microsoft Excel Imagine Excel was fun! Excel offers plenty of scope for projects that go beyond its intended use. The only limit is your imagination. Here are the most creative examples of how people are using Excel. Read More , but it does serve as a great example of how to combine a few functions to solve a problem.

2. Pulling Numbers Out of Mixed Strings

A similar question was asked by reader Yadhu Nandan:

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

Example is – 4552dfsdg6652sdfsdfd5654

I want 4552 6652 5654 in different cells

Another reader, Tim K SW, provided the perfect solution to this particular problem:

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)

The formulas are fairly simple, but if you’re not familiar with the MID function, you might not understand how it works. MID takes three arguments: a cell, the character number where the result starts, and the number of characters that should be pulled. MID(A1,10,4), for example, tells Excel to take four characters starting at the tenth character in the string.

excel-mid-function

Using the three different MID functions in three cells pulls the numbers out of this long string of numbers and letters. Obviously, this method only works if you always have the same number of characters — both letters and numbers — in each cell. If the number of each varies, you’ll need a much more complicated formula.

3. Getting a Number from a Mixed String with Spaces

One of the most difficult requests posted on the article was this one, from reader Daryl:

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 spent some time working on this one, and wasn’t able to come up with a solution myself, so I took to Reddit. User UnretiredGymnast provided this formula which is long and very complex:

=SUMPRODUCT(MID(0&LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),LARGE(INDEX(ISNUMBER(--MID(LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),ROW($1:$99),1))*ROW($1:$99),0),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

As you can see, decoding this formula takes quite a while, and requires a pretty solid knowledge of a lot of Excel functions 16 Excel Formulas that Will Help You Solve Real Life Problems 16 Excel Formulas that Will Help You Solve Real Life Problems The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them. Read More . To help figure out exactly what’s going on here, we’ll need to look at a few functions you might not be familiar with. The first is IFERROR, which catches an error (usually represented with a pound sign, like #N/A or #DIV/0) and replaces it with something else. In the above, you’ll see IFERROR(SEARCH(“%”,A1)-4,LEN(A1)). Let’s break this down.

IFERROR looks at the first argument, which is SEARCH(“%”,A1)-4. So, if “%” appears in cell A1, its location is returned and four is subtracted from it. If “%” does not appear in the string, Excel will create an error, and the length of A1 is returned instead.

excel-mega-function

The other functions you might not be familiar with are a bit simpler; SUMPRODUCT, for example, multiplies and then adds elements of arrays. LARGE returns the largest number in a range. ROW, combined with a dollar sign, returns an absolute reference to a row.

Seeing how all of these functions work together isn’t easy, but if you start in the middle of the formula, and work outwards, you’ll start to see what it’s doing. It’ll take a while, but if you’re interested in seeing exactly how it works, I recommend putting it into an Excel sheet and playing around with it. That’s the best way to get an idea of what you’re working with.

(Also, the best way to avoid a problem like this is to import your data more neatly How to Import Data Into Your Excel Spreadsheets the Neat & Easy Way How to Import Data Into Your Excel Spreadsheets the Neat & Easy Way Have you ever struggled with importing or exporting data into a spreadsheet? This tutorial will help you master the art of moving data between Microsoft Excel, CSV, HTML, and other file formats. Read More  — it’s not always an option, but it should be your first choice when it is.)

One Step at a Time

As you can see, the best way to solve any Excel problem is one step at a time: start with what you know, see what it gets you, and go from there. Sometimes you’ll end up with an elegant solution, and sometimes you’ll get something that’s really long, messy, and complex. But as long as it works, you’ve succeeded!

And don’t forget to ask for help Need to Learn Excel? 10 Experts Will Teach You for Free! Need to Learn Excel? 10 Experts Will Teach You for Free! Learning how to use Excel's more advanced features can be tough. To make it a little easier, we have tracked down the best Excel gurus who can help you master Microsoft Excel. Read More . There are some extremely talented Excel users out there, and their help can be invaluable in solving a tough problem.

Do you have any suggestions on solving tough extraction problems? Do you know of any other solutions to the problems we tackled above? Share them and any thoughts you have in the comments below!

  1. Karl-Heinz Frickel
    September 4, 2016 at 11:39 am

    Hi, I have a set of geographical positions given in a form of
    Point Latitude N-S Longitude W-E Distance
    n. 0 S 30°59' 31.98" E 30°15' 35.12" 19,7 m
    n. 1 S 30°59' 32.12" E 30°15' 34.39" 8,4 m
    n. 2 S 30°59' 31.90" E 30°15' 34.19" 5,8 m
    n. 3 S 30°59' 31.77" E 30°15' 34.04" 7,1 m
    and there can be n-points. For the first 2 it is easy to observe that they are showing a straight line . At n. 1 now a line will take a curve at which angle towards n.2?
    And can this line go straight towards n.3 ? And if not, at what angle?
    I want all figures and angles in a x-y cartesian sheet with the relevant data and also being able to make a diagram with these dots.
    Any help is very much appreciated.

Leave a Reply

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