Extracting numbers from registration marks

[Terry Bennett]

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.

 

[RagDyeR]

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&"0123456789")),ROW(INDIRECT("1:256"))))
--

HTH,

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


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.

 

[JLatham]

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(ISNUMBER(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.

 

[JLatham]

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.

 

[Terry Bennett]

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

 

[Tyro]

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

 

[Bob Phillips]

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&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))

--
HTH

Bob

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

 

[Bob Phillips]

You can also do it without LOOKUP

=MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),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]

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&"0123456789")),ROW(
1:50)))

In the formula bar ... select:

MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),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&"0123456789")),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&"0123456789")),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&"0123456789")),
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!