I have a huge list of products that have different model types denoted by
letters at the end of the product i.e.
ProductA 5760tr
ProductA 5760xsn
etc.

I eventually want to do a vlookup but only have "ProductA 5760" in my data
to be pulled from. Can I get rid of all the letters after the numbers even
if they're variable in length?

What I have:
ProductA 5760tr
ProductA 5760xsn
ProductB 7560tr

What I want:
ProductA 5760
ProductA 5760
ProductB 7560

Thanks for any help!
--
-SA

The following UDF starts at the end of the string and discards characters
until it finds a numeral:

Function strip_it(r As Range) As String
v = r.Value
L = Len(v)
For i = 1 To L
If Not IsNumeric(Right(v, 1)) Then
v = Left(v, Len(v) - 1)
Else
Exit For
End If
Next
strip_it = v
End Function

--
Gary''s Student - gsnu200795


"StumpedAgain" wrote:

I have a huge list of products that have different model types denoted by
letters at the end of the product i.e.
ProductA 5760tr
ProductA 5760xsn
etc.

I eventually want to do a vlookup but only have "ProductA 5760" in my data
to be pulled from. Can I get rid of all the letters after the numbers even
if they're variable in length?

What I have:
ProductA 5760tr
ProductA 5760xsn
ProductB 7560tr

What I want:
ProductA 5760
ProductA 5760
ProductB 7560

Thanks for any help!
--
-SA

You could use this array-entered** formula to get the part of the text you
want...

=LEFT(A1,MAX(ISNUMBER(--MID(A1,ROW($1:$99),1))*ROW($1:$99)))

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

The 99 in the two occurrences of ROW($1:$99) within the formula only need to
be a number larger than the maximum number of characters you will ever
expect to have in one of your products.

Rick


"StumpedAgain" wrote in message
...
I have a huge list of products that have different model types denoted by
letters at the end of the product i.e.
ProductA 5760tr
ProductA 5760xsn
etc.

I eventually want to do a vlookup but only have "ProductA 5760" in my data
to be pulled from. Can I get rid of all the letters after the numbers
even
if they're variable in length?

What I have:
ProductA 5760tr
ProductA 5760xsn
ProductB 7560tr

What I want:
ProductA 5760
ProductA 5760
ProductB 7560

Thanks for any help!
--
-SA

Nice! I had to change

If Not IsNumeric(Right(v, 1)) Then
to
If IsNumeric(Right(v, 1)) = False Then

but other than that, worked like a charm. Thanks!

--
-SA


"Gary''s Student" wrote:

The following UDF starts at the end of the string and discards characters
until it finds a numeral:

Function strip_it(r As Range) As String
v = r.Value
L = Len(v)
For i = 1 To L
If Not IsNumeric(Right(v, 1)) Then
v = Left(v, Len(v) - 1)
Else
Exit For
End If
Next
strip_it = v
End Function

--
Gary''s Student - gsnu200795


"StumpedAgain" wrote:

I have a huge list of products that have different model types denoted by
letters at the end of the product i.e.
ProductA 5760tr
ProductA 5760xsn
etc.

I eventually want to do a vlookup but only have "ProductA 5760" in my data
to be pulled from. Can I get rid of all the letters after the numbers even
if they're variable in length?

What I have:
ProductA 5760tr
ProductA 5760xsn
ProductB 7560tr

What I want:
ProductA 5760
ProductA 5760
ProductB 7560

Thanks for any help!
--
-SA

if u have always productA, B, C etc why dont u try left(range;11)
mcg

U¿ytkownik "StumpedAgain" napisa³ w
wiadomo¶ci ...
I have a huge list of products that have different model types denoted by
letters at the end of the product i.e.
ProductA 5760tr
ProductA 5760xsn
etc.

I eventually want to do a vlookup but only have "ProductA 5760" in my data
to be pulled from. Can I get rid of all the letters after the numbers
even
if they're variable in length?

What I have:
ProductA 5760tr
ProductA 5760xsn
ProductB 7560tr

What I want:
ProductA 5760
ProductA 5760
ProductB 7560

Thanks for any help!
--
-SA