Here's a big dumb formula I made for stripping out the number at the end of a text string in certain scenarios. Seems like it might be a fairly common scenario, so I thought I'd share.
Purpose:
Sometimes I'll get reports where a column contains customer names and numbers in the format "Customer Name (Number)", and I need that Number. That's all fine and dandy and I could just use a MID/FIND combo or text-to-columns on "(" and ")", however sometimes the data is in a "Customer Name (Arbitrary Junk) (Number)", and that ruins everything because it's not consistent and it's annoying. Saving grace: In this scenario, the common factor is the number I need is always in the last set of ()'s
So, I made a formula, and ended up making it long and complicated because I got tired of manually changing the cell references all the time because I'm lazy - this formula is therefore completely dynamic. Paste this in the cell immediately to the RIGHT of the cell with the string you want to pull a number from. As long as your number isn't in column XFD, it should work just fine.
Note: This also maintains any leading zeros in the number at the end, if they exist. Also, perhaps contrary to it's appearance, it's pretty quick - just tested on 50k cells, calculation time 1 second on an older Core i5M. Also, if your cell contains a =CHAR(2) it'll give an error [this is unlikely to come up in most scenarios, but if it does just change all the char(2)'s in the formula to anything you know won't ever exist, and it'll work just fine]
Enjoy
Bookmarks