I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I need
'456'

Thanks.

With plate numbers in Column A, starting in A2, enter this in B2 and copy
down as needed:

=LOOKUP(99^99,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:256"))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Terry Bennett" wrote in message
...
I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I need
'456'

Thanks.

Example for a registration number in A1, this formula in another cell will
give results for you two example "mostly" layouts:

=IF(ISNUMBER(VALUE(LEFT(A1,3))),LEFT(A1,3),IF(ISNU MBER(VALUE(MID(A1,4,3))),MID(A1,4,3),"Not a Standard Format"))

That will take care of the two most often used formats. You could modify the
"Not a Standard Format" final not-true condition to contain more nested IF()s
up to the max of 7 levels of nesting (Excel 2003 and earlier) based on what
is already displayed.

"Terry Bennett" wrote:

I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I need
'456'

Thanks.

I think I like RagDyer's solution better - if I'd seen it before posting
mine, I probably wouldn't have even put mine up.

"JLatham" wrote:

Example for a registration number in A1, this formula in another cell will
give results for you two example "mostly" layouts:

=IF(ISNUMBER(VALUE(LEFT(A1,3))),LEFT(A1,3),IF(ISNU MBER(VALUE(MID(A1,4,3))),MID(A1,4,3),"Not a Standard Format"))

That will take care of the two most often used formats. You could modify the
"Not a Standard Format" final not-true condition to contain more nested IF()s
up to the max of 7 levels of nesting (Excel 2003 and earlier) based on what
is already displayed.

"Terry Bennett" wrote:

I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I need
'456'

Thanks.

Guys

Huge thanks for your suggestions.

I have copied RagDye's solution and it works perfectly. Must admit,
however, I don't understand some of the functions used.

What, in particular, does the 99^99 refer to?!

Terry

"Terry Bennett" wrote in message
...
I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I need
'456'

Thanks.

99 exponentiated to the 99 power, creating a very large number: 3.6973E+197
2^0 = 1, 2^1 = 2, 2^2 = 4, 2^3=8, etc

Tyro

"Terry Bennett" wrote in message
...
Guys

Huge thanks for your suggestions.

I have copied RagDye's solution and it works perfectly. Must admit,
however, I don't understand some of the functions used.

What, in particular, does the 99^99 refer to?!

Terry

"Terry Bennett" wrote in message
...
I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I
need '456'

Thanks.

The premise is to lookup a very large number which should never be found, so
lookup returns the largest number that is less than that value. As the MID
part of the formula retrieves an array of strings building up from the
first numeric character, it returns the one that is the complete numeric
portion.

For instance 123ABC returns {"1";"12";"123";"123A";"123AB";"123ABC"},
preceded by -- makes it {1;12;123;#VALUE!;#VALUE!;#VALUE!}, the biggest
being 123. Similarly, XY789ZZ returns
{"7";"78";"789";"789Z";"789ZZ";"789ZZ";"789ZZ"} , the -- makes it
{7;78;789;#VALUE!;#VALUE!;#VALUE!;#VALUE!}, the biggest number being 789.

I would change it just a little

=LOOKUP(99^99,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2)))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Terry Bennett" wrote in message
...
Guys

Huge thanks for your suggestions.

I have copied RagDye's solution and it works perfectly. Must admit,
however, I don't understand some of the functions used.

What, in particular, does the 99^99 refer to?!

Terry

"Terry Bennett" wrote in message
...
I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I
need '456'

Thanks.

You can also do it without LOOKUP

=MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"01234 56789")),SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"RagDyeR" wrote in message
...
With plate numbers in Column A, starting in A2, enter this in B2 and copy
down as needed:

=LOOKUP(99^99,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:256"))))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Terry Bennett" wrote in message
...
I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I need
'456'

Thanks.

To add to Bob's very lucid explanation, an excellent way to actually *see*
what a particular formula is doing is to select parts of it (individual
functions or various combinations) in the formula bar, and then hit <F9.

This displays the individual performances of the various functions, or their
results when combined.

I did screw up using that LARGE Row array at the end, which Bob picked up on
and revised.
I was trying something and forgot to change it.

BUT, we can use that to see what the formula is doing.

Let's use this formula for an example:

=LOOKUP(99^99,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(
1:50)))

In the formula bar ... select:

MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(1:50))

And then hit <F9

Be sure to hit <Esc when you're done looking, because hitting <Enter will
destroy the formula in that cell!

Now, include the unary in the selection, and see what <F9 displays.

--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(1:50))

You'll see just about what Bob described.

Change the formula by reducing the size of the Row array to:

=LOOKUP(99^99,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(
1:25)))

Now, when you select the Mid() function, with and without the unary, and hit
<F9, you see the *elimination* of all that *duplication* at the end.

Bob's revision sizes the elements in the array to almost only what is
necessary for the individual cell's contents, eliminating a great deal of
duplication that is created when an arbitrary guess is used for the array
size:

=LOOKUP(99^99,--MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"01234567 89")),
ROW(INDIRECT("1:"&LEN(A3)))))

The point here being, use the <F9 evaluation technique to *see* what your
formula is actually doing.

When you create a formula and hit <Enter, and all you get is an error
message, OR, an incorrect return, this method can help you a lot in making
the proper revisions.

This will not tell you WHY something is taking place, but it WILL tell you
WHAT is happening!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Terry Bennett" wrote in message
...
Guys

Huge thanks for your suggestions.

I have copied RagDye's solution and it works perfectly. Must admit,
however, I don't understand some of the functions used.

What, in particular, does the 99^99 refer to?!

Terry

"Terry Bennett" wrote in message
...
I have an extensive list of vehicle registration plates in column A of a
worksheet. They are in various formats but mostly:

NNNLLL
LLLNNNL

How would I go about extracting just the numbers from the registrations?
ie; for registration 123ABC I just want to return '123', or ABC456D I
need
'456'

Thanks.