Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Return Number Before Alpha Character

  1. #1
    Join Date
    18th October 2007
    Posts
    10

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2007
    Posts
    759

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th October 2007
    Posts
    10

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Extract String After First Alpha Character
    By EROEI in forum EXCEL HELP
    Replies: 3
    Last Post: July 27th, 2007, 05:11
  2. Next Alpha Number Formula
    By wingam00 in forum EXCEL HELP
    Replies: 3
    Last Post: July 18th, 2006, 22:06
  3. test first character in each cell to see if alpha
    By wbsmith in forum EXCEL HELP
    Replies: 4
    Last Post: March 15th, 2005, 00:36
  4. Alpha Character to return 2 digit number.
    By Tbone in forum EXCEL HELP
    Replies: 7
    Last Post: August 14th, 2004, 22:12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno