MID and FIND Function for Numbers
 

Hello,

I have a list of about 300 names that I want to shorten.
I want to use a combination of the mid and find function, but I don't know
what wildcard to use for numbers.
What formula can I use for the original text below to get the result that I
want?

Hope you guys can help, I can't find any solution at all!

Original Text Result
ROCK SPIKE 9 LAND&SEA ROCK SPIKE
BOOT COMFORT NON-ZIP 12 LAND&SEA BOOT COMFORT NON-ZIP
BOOT BEACH ZIP NEO 9 LAND&SEA BOOT BEACH ZIP NEO

MID and FIND Function for Numbers
 

Give this a try...

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))-1))

Rick


"alxcl" wrote in message
...
Hello,

I have a list of about 300 names that I want to shorten.
I want to use a combination of the mid and find function, but I don't know
what wildcard to use for numbers.
What formula can I use for the original text below to get the result that
I
want?

Hope you guys can help, I can't find any solution at all!

Original Text Result
ROCK SPIKE 9 LAND&SEA ROCK SPIKE
BOOT COMFORT NON-ZIP 12 LAND&SEA BOOT COMFORT NON-ZIP
BOOT BEACH ZIP NEO 9 LAND&SEA BOOT BEACH ZIP NEO

MID and FIND Function for Numbers
 

I love this formula, I can apply it to sooo many things...now what to do with
all the time I'll save mmmm.....

Great question - great answer, thanks both.

"Rick Rothstein (MVP - VB)" wrote:

Give this a try...

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))-1))

Rick


"alxcl" wrote in message
...
Hello,

I have a list of about 300 names that I want to shorten.
I want to use a combination of the mid and find function, but I don't know
what wildcard to use for numbers.
What formula can I use for the original text below to get the result that
I
want?

Hope you guys can help, I can't find any solution at all!

Original Text Result
ROCK SPIKE 9 LAND&SEA ROCK SPIKE
BOOT COMFORT NON-ZIP 12 LAND&SEA BOOT COMFORT NON-ZIP
BOOT BEACH ZIP NEO 9 LAND&SEA BOOT BEACH ZIP NEO