Rudy Rodarte . com

Information

This article was written on 14 Jul 2011, and is filled under Programming, Windows.

Current post is tagged

, , ,

Find numbers within a string in Excel

Recently, I had to pull customer numbers out of strings from an excel sheet. Sure,  you can use trickery like spaces to columns or regex. Excel is pretty powerful, if you know all of the text tools at your disposal. This is how I extracted the numbers for random text


The problem was, each row was formatted differently. So I had several thousand rows that looked like this:

Client A (555)
Client B (789) Houston
Client Dallas (4444)

and so on.

I looked around on some Excel Guru sites and found the following formula.

=REPLACE(LEFT(A1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(A1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0

Caveat

Though not perfect, this was exactly what I needed. The one drawback to this code is any cell with non sequential numbers such as:
Client T1000 Loop 360
will not work.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

Leave a Reply