Return Number Before Alpha Character

[thelarster]

I have an Excel file that quiries Google for driving distance (someone helped me with this.) It returns the correct miles but has some 'extra' characters.

Example:
31.1&nb or 886&nbs

So the answer always has &nbs after the mileage. I tried the following function which works when there is a decimal but fails when there is not.

=REPLACE(MID(E2,1,SEARCH("&",E2,1)-1),SEARCH(".",E2,1),1,",")

Can this be done with a function or must it be VB script? Which is fine if it is.

Thanks!

[turtle44]

Why are you searching for a decimal? It seems that all you need is everything to the left of the ampersand. If that is the case, this should work:
=LEFT(E2,FIND("&",E2,1)-1)

---

[thelarster]

turtle44 - I don't know why I was - but THANK YOU! I am new to search/finding functions in Excel and couldn't figure this out. Your solution worked.

[Dave Hawley]

If you wish to continue using this free service.

Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.

In future, please take 1 minute of your time to read the text on the New Thread page.

Please take just 3 mins of your time to read: How To Get Your Question Answered...FAST! AND Anatomy Of A Good Thread Title

REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.