Using The Search Formula in Excel

Here’s a little known trick about Excel that will come as a pleasant surprise to folks who have spent many an hour in unravelling ‘messy’ data.

Say you’re working with an Excel sheet that has a data set as seen below.  Numbers have been fed into a column, but the formatting is not consistent. Some entries read (Roll_No_1) while others are (R12). What you need at the end of the day is just the number, without any of the preceding text. 

The easy, simplistic and slightly crude way would be to look at each cell and make a note of the the numbers, but good luck to you if you are handling admissions at a university, with around 13,000 applications to go. That’s a very long night indeed.

But there is a quicker way around this conundrum, and it’s quite simple too!

Excel has this formula called the SEARCH formula – all it does is look up a particular piece of text within another piece of text. So if you asked it to search for the letter ‘e’ in ‘text’, it would come up with the answer ’2′. 

After that, all you need to do is use the ‘RIGHT’ function to arrive at just what you need – the numbers themselves.

Painless, quick and simple, right? Try it the next time you’re working with data that is tricky to navigate – you’ll be surprised at how powerful this simple formula can be.

Do you know of any other uses of SEARCH that totally rock? Share ‘em with us! Let us know in the comments below!