Microsoft Excel – getting a number out of a text string

It may be that you have strings such as abc134d and ab76543de and you need to just get out the numbers. Unless there is a definite pattern as to where the numbers are in the string and how many numbers there are, this has to be done in more than one stage.

A lot of answers you will find on the web, use complex array formulas, but provided you have space on your worksheet, this can be done without too many complicated formulas.

In the example below, I have two strings of varying lengths and where the numbers are not in the same place.

Select both cells, or as many as you need to work with, then on the Data tab, in the Data Tools group, click Text to Columns. Select Fixed Width and click Next.

Create break lines between each character, then click Next. Click Finish. Your string will now be in separate columns in the worksheet – make sure you have enough blank columns first or select a suitable first destination cell before clicking Finish.

In the first blank column to the right, at the first row with the string in, create the following formula:

=IF(ISNUMBER(A1),A1,””)

Drag across for as many cells as there are characters, and also down for the number of appropriate rows. You should then see something like this.

In another blank cell type the following formula, adapted for which cells you are using:

=M1&N1&O1&P1&Q1&R1

This will give just the number part of the string.

Admittedly a lot of columns are used in the creation of the final result, but it may be easier to follow than some of the more complex ways to achieve the same thing.

Find out more about our Microsoft Excel training Sydney, Australia.

Find out more about our Microsoft Excel training London, UK, or our Microsoft Excel training Dorset, Hampshire, Wiltshire.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s