As part of my now ongoing project to create a comprehensive drawing register,
I need to list the latest revision of a drawing ever issued. These may or may
not be in order, as sometime there will be a need to issue an older drawing.

In a row, I will have letters (A - Z, AA - AZ), Numbers (01 - 99) and / or (1
- 99), as well as /, \ or -.

I currently use the following function (where I29 to DS29 is my range):
=IF(SUM(--ISTEXT(I29:DS29)),CHAR(MAX(IF(I29:DS29<"",CODE(I2 9:DS29),""))),MAX
(I29:DS29))

This returns the highest numeric number from 1 - 9, and then from A - Z.

However, it only registers up to number 9. Any numbers over 9 will return
it's multiple. Eg. 11 will be shown as 1, 25 will be shown as 2, 33 as 3 and
so on.

It will correctly display the alphabetic value up to Z, however, the same
applies to double letters. AA, AT, AZ will all return as A. The only ranges I
can work with then are 1 - 9 and A - Z. The function does pick up the
backslash \, and it does pick up / and - if I use apostrophe before, but I
don't want to use this, as users will be entering info and will forget to put
the apostrophe in.

How do I make it so that the function will return the values, in addition to
1 -9 and A - Z, of the numbers from 10-99, double letters AA - AZ, as well as
any keyboard symbols.

TIA

David

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200612/1

try match(999999999999
or match("zzzzzzzzzzzz"

--
Don Guillett
SalesAid Software

"De-coi via OfficeKB.com" <u29929@uwe wrote in message
news:6a5c973c9b1fc@uwe...
As part of my now ongoing project to create a comprehensive drawing
register,
I need to list the latest revision of a drawing ever issued. These may or
may
not be in order, as sometime there will be a need to issue an older
drawing.

In a row, I will have letters (A - Z, AA - AZ), Numbers (01 - 99) and / or
(1
- 99), as well as /, \ or -.

I currently use the following function (where I29 to DS29 is my range):
=IF(SUM(--ISTEXT(I29:DS29)),CHAR(MAX(IF(I29:DS29<"",CODE(I2 9:DS29),""))),MAX
(I29:DS29))

This returns the highest numeric number from 1 - 9, and then from A - Z.

However, it only registers up to number 9. Any numbers over 9 will return
it's multiple. Eg. 11 will be shown as 1, 25 will be shown as 2, 33 as 3
and
so on.

It will correctly display the alphabetic value up to Z, however, the same
applies to double letters. AA, AT, AZ will all return as A. The only
ranges I
can work with then are 1 - 9 and A - Z. The function does pick up the
backslash \, and it does pick up / and - if I use apostrophe before, but I
don't want to use this, as users will be entering info and will forget to
put
the apostrophe in.

How do I make it so that the function will return the values, in addition
to
1 -9 and A - Z, of the numbers from 10-99, double letters AA - AZ, as well
as
any keyboard symbols.

TIA

David

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200612/1

Thanks Don.

If I look at Excel Help, and then try this, it returns the position of the
value, and not the value itself (as determined by Match_Type (-1, 0 or 1).

If I am meant to integrate this into the function I posted above, then could
you please explain how I would do this further. My Excel function knowledge
is poor...

Don Guillett wrote:
try match(999999999999
or match("zzzzzzzzzzzz"

As part of my now ongoing project to create a comprehensive drawing
register,
[quoted text clipped - 36 lines]

David

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200612/1