1. Extraction Using a Separator

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

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

2. Pulling Numbers Out of Mixed Strings

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.

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:

As you can see, decoding this formula takes quite a while, and requires a pretty solid knowledge of a lot of Excel functions16 Excel Formulas that Will Help You Solve Real Life Problems16 Excel Formulas that Will Help You Solve Real Life ProblemsThe 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.

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.

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!

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

Comment

Name *

Email *

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.