Excel Filter: Erase all but second numeric value in cell

January 9, 2015 3:56 PM

I have a range of cells in Excel that all have the same text, but within the text are two numeric values. I want to remove everything besides the second numeric value.

Example, I have a text that reads

Age of update - 41. Last update 5 days ago.

And I just want to keep the 5. The issue I'm having is that that both numbers will be volatile, each cell will have different numbers, from 1 to 3 digits, such as 123 days ago. The text, however, will always have the exact same format.

I have thought of using substitute, using the Left and Right functions, but I'm not the most Excel savvy and I can't figure out how to remove all but what I want.

Any ideas?

posted by motioncityshakespeare to Technology (15 answers total)

will the ages all be two digits?

You could use =MID(A1,33,10) to get a result that starts with the number of days.

Then paste the results as values and use text-to-columns delimited by a space to separate out the number.

Even if some ages are 1 or 3 digits, it'll probably be easy enough to do those groups separately.
posted by mullacc at 4:02 PM on January 9, 2015

If the text is always the same, here's a pretty simple way to do it:
=MID(A1,FIND("Last update",A1)+12,FIND(" days ago",A1)-FIND("Last update",A1)-12)

which in English is:
-Take some text from the middle of this string
-Find the text "Last update" and add 12 characters to the beginning of that: start there [this accounts for the "age of update" number changing # of characters too]
-Take the number of characters between the text "Last update" +12 characters, and the text " days ago" [note the space].

In this example, you end up pulling out "5". If your 2 numbers change radically, you should still end up with the right number.
posted by brainmouse at 4:07 PM on January 9, 2015 [2 favorites]

Response by poster: brainmouse, does that take into account the first sentence, the 'Age of update' bit? Because when I'm trying this I'm not getting anywhere and I'm not quite sure why. I'm getting #value!
posted by motioncityshakespeare at 4:27 PM on January 9, 2015

Yes it does, but you need to make sure you're referring to the right cell, which I didn't point out. In that example I assumed your text was in cell A1, if it's in a different cell you need to replace all the "A1"s with wherever your first cell of data is. I suspect that's what's giving you the #value error.
posted by brainmouse at 4:29 PM on January 9, 2015

Find and replace is a simple way of doing it if all the text is the same, leave the replace field blank. (I don't have excel to text it on right now but I'm pretty positive I've used it like that)

Oops, missed that there are two variable numbers. Sorry, this isn't useful then.
posted by platypus of the universe at 4:43 PM on January 9, 2015 [1 favorite]

Can you do text to column and break it up? So create 5 columns.

Column 1: Age of update
Column 2: 00
Column 3: Last Update
Column 4: 0
Column 5: Days ago

Then get rid of Columns 1, 2, 3, and 5

Boo-yah!
posted by Ruthless Bunny at 4:50 PM on January 9, 2015 [2 favorites]

If you're having trouble with my formula and want try something formula-free, you can combine text to columns and find and replace - I wouldn't use either on their own because because text to columns will create too many messy columns (and you'll have to split on multiple values, both " " and "." - you can't just split "Age of update" into one column automagically, and fixed width won't work since some columns aren't), and find and replace can't account for the 2 variable numbers.

So what you can do is:

Find "Age of update - " - replace with nothing (just leave the replace field blank)
Find ". Last update" - replace with nothing
Find " days ago." - replace with nothing

You'll end up with "41 5" (I purposefully kept that space in between the two numbers - make sure you don't delete everything other than the numbers or they'll just meld together).

Then you can do text to columns and you'll just end up with 1 column per number, and you can delete the excess column from there.
posted by brainmouse at 4:58 PM on January 9, 2015 [1 favorite]

Building on brainmouse's idea, how about finding and replacing the text with a comma, period or letter so that you don't accidentally run the numbers together? Then you can do text to columns.
posted by Wet Hen at 5:08 PM on January 9, 2015 [2 favorites]

As people are saying, find & replace + text to columns is probably the easiest way of doing this. I'd start by getting rid of the leading text using find & replace with a blank replace field. Then split the resulting data into 2 columns: the first number, and the rest of the text. You can use the SEARCH() function to split the text since you always know what character will follow the first number, regardless of the first number's length. Then do the same process on the second column which holds text + the second number.

Don't try to split it all in one function. That way lies madness. Also, you're doing this in a copy of the spreadsheet so you can't lose data, right?
posted by Tehhund at 4:33 AM on January 10, 2015

If I was wasting time trying to do this in Excel, I'd be tempted to write the block of cells out to a .txt file, and change them in an editor like Notepad, then bring the results back to Excel.
posted by SemiSalt at 7:02 AM on January 10, 2015

I agree with SemiSalt, except that I would use perl instead of Notepad.
posted by Bruce H. at 11:19 AM on January 10, 2015

You can make the find and replace method even quicker:

Find and replace "*te" with nothing

Find and replace " da*" with nothing (note space before the d)

Done.

* acts as a wildcard for any number of characters.
posted by protorp at 12:21 PM on January 10, 2015

Response by poster: Okay, sorry for the wait in this update, this is for work and I don't work on the weekends.

It needs to be a formula. This will be for my whole office and they can't be trusted to do find and replace. It can be multiple formulas, or nested formulas, but it has to be a formula. I can insert a blank sheet for putting data if need be.

This makes it more difficult I think and I'm still lost.

I would be fine with splitting the sentences on the back end and then finding the number in the second sentence. My issue is I can't find a way to do, for instance, a Substitute formula and then edit it, since the cell does not have the actual sentence, just a formula that pulls the sentence, if that makes sense.

Any more help you guys can give will be greatly appreciated.
posted by motioncityshakespeare at 10:56 AM on January 12, 2015

Best answer: OK, in that case, my formula definitely does work, as long as the text is always the same and only the numbers change. If you put one of these sentences in cell A1, and paste my formula into B1, it will give you the number you want, regardless of the length of the first and second numbers:

=MID(A1,FIND("Last update",A1)+12,FIND(" days ago",A1)-FIND("Last update",A1)-12)

If you try that (with the sentence in A1 and the formula in B1), are you still getting a #value or other error?
posted by brainmouse at 11:54 AM on January 12, 2015

Response by poster: Brainmouse, you are lovely. Thank you very much.
posted by motioncityshakespeare at 12:09 PM on January 12, 2015