Solved: Selecting Definite Number of Characters in a Cell

I have a description for a couple trucks in my inventory, rather than retyping all the mileage, I would like to select the miles out of the description.

Example:
14.0 Detroit 515 HP, 10 Spd OD, Engine Brake, Air Ride Suspension, 3.42 Ratio, 234" WB, 70" Double Deck Condo, 22.5LP Tires, Aluminum/Steel Wheels, Tandem Axle, 12,000# FA, 40,000# RA, 259,000 Miles

I have tried the find, left and mid formulas but haven't put something together to work just right. I would like to select the 7 characters to the left of "Miles".

Does anyone have any ideas?

If you text string is in A1, try:

Please Login or Register  to view this content.

replace A1 as required and copy down and format cell to number with 1000s separator

This should work even if the mileage is not 7 digits long.

Try

Please Login or Register  to view this content.

Try this:

Please Login or Register  to view this content.

If A1 contains: 259,123 miles, engine brake
...the formula returns: 259123

If A1 contains: Engine brake, 259,123 miles, something else
...the formula returns: 259123

Does that help?

My formula made the assumption that the mileage may not always be 7 chars long....

The formula I posted does have its limits.
It only works from 10,000 through 999,999.

This variation covers from 10 through 9,999,999

Please Login or Register  to view this content.

Cool... mine covers from 0 to 9.99999999999e+307