Announcement

Collapse
No announcement yet.

Return Number Before Alpha Character

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Return Number Before Alpha Character



    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!

  • #2
    Re: Replace Function - Remove X Number

    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)

    Comment


    • #3
      Re: Replace Function - Remove X Number

      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.

      Comment


      • #4


        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.

        Comment

        Working...
        X