Extract numbers from a string

**andyiain** · 09-06-2006, 05:32 AM

Hi

I have a list of alphanumeric codes of the form letternumber (i.e. no spaces or delimiters between the two sections) where the letter part is either 2, 3, or 4 characters and the number part is either 2 or 3 characters, so the whole string is anything from 4 to 7 characters. I'd like to be able to be able to strip off the letters so I'm just left with number part.

Can anyone figure out a way to do this?

Regards
Andy.

**raypayette** · 09-06-2006, 05:37 AM

As you stated "the whole string is anything from 4 to 7 characters" that recognizes that every character is a string. The 2 and 3s can either be a string or a number. How can they possibly be extracted as you wish if there is no way to distinguish them apart?

**Saz** · 09-06-2006, 05:59 AM

Don't know if this go someway to helping you.

But if there are in a set order so for example if the following is in A1

AEFBF23

You could use

=leftb(A1,5) [this will extract the letters]

=rightb(A1,2) [this will extract the numbers]

=mid(A1,3,2) [this will extract, starting at the 3 character, 2 characters. Answer in this case would be BF]

But if there are different lengths you may need some whizzy VBA code

Just thought I would chip in a suggestion

**andyiain** · 09-06-2006, 06:07 AM

Hi raypayette, thanks for getting back to me.

The number part of the string is distinguished as they are numbers not the letters that form the start of the string. I tried to use Isnumber to evaluate the final three characters hoping that would give me a true when all three were numbers and false when it was a letter and two numbers but it resulted in false both times (I imagine because of the format).

In response to saz the case is as you fear as the number part can be two or three characters long and the letter part 2, 3 or 4 it's not possible to use left, right or mid on their own (I don't think).

Andy.

**raypayette** · 09-06-2006, 06:11 AM

I still don't understand since "The number part of the string is distinguished as they are numbers not the letters that form the start of the string." There are no more numbers if they are part of a string!

**andyiain** · 09-06-2006, 06:48 AM

Hi

I can see what you mean but, for example, in the code CSW04 there is a 'letter' part, the CSW and a 'number' part, 04. While the 04 isn't strictly a number is does have meaning in itself and is distinguished by being constructed of characters one would describe as numbers (even though as far as Excel is concerned they are not numbers). My problem is precisly that Excel would not recognise it as a number but this does have meaning and I'd like to isolate it if possible.

Andy.