+ Reply to Thread
Results 1 to 7 of 7

lookup and change value in 1 formula

  1. #1
    Registered User
    Join Date
    11-01-2011
    Location
    Twente, Nederland
    MS-Off Ver
    Excel Professional Plus 2019
    Posts
    40

    lookup and change value in 1 formula

    Hi guys.

    Have an interesting challenge.

    I am gathering information on pricing from my suppliers into a spreadsheet.
    One of my suppliers delivers a pricelist where the values in the column price are like: "4,90€/kg"
    In my spreadsheet I need these values, but I need them to be numeric so I can calculate sales prices from them.

    I can get them in by using a simple vlookup... but then all text characters need to be removed from the values, preferrably in the same formula, so once its done it will keep working on new input.
    Any ideas?
    carpe diem

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: lookup and change value in 1 formula

    A solution for this kind of problem requires more than just one piece of test data.

    Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-01-2011
    Location
    Twente, Nederland
    MS-Off Ver
    Excel Professional Plus 2019
    Posts
    40

    Post Re: lookup and change value in 1 formula

    Ok,here's a mockup.

    The sheet "Processing"column D contains the Vlookup that needs to be modified so that text characters are removed before storing the value.
    If a solution works... the formula in the column "Sales price" will show the correct calculated values.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    876

    Re: lookup and change value in 1 formula

    Hi pluginguin,

    Try the following in cell D2:

    Please Login or Register  to view this content.
    That should extract the number but as text. To convert it to a number use:

    Please Login or Register  to view this content.
    This should work but I can't test it because my machine uses periods instead of commas in numbers.

    If you need the commas changing to periods as well let me know.

    Hope this helps,

    Snook
    Last edited by The_Snook; 11-15-2019 at 05:35 AM.

  5. #5
    Registered User
    Join Date
    11-01-2011
    Location
    Twente, Nederland
    MS-Off Ver
    Excel Professional Plus 2019
    Posts
    40

    Re: lookup and change value in 1 formula

    Yes, your last formula does the trick, thanks...

    But I suspect that I will get lists from other suppliers that also have numbers & text as a value, but it might not be in the same format.
    So not all values will have text starting with "€".... could be an entirely other character.

    So I am looking for a more general way of separating text from numbers in combination with a vlookup.
    Can I replace the "€" bit in the formula with a dynamic value for any text character?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: lookup and change value in 1 formula

    This will extract the number from the beginning of an alphanumeric text string:

    =-LOOKUP(1;-LEFT(D2,ROW($1:$100)))
    Glenn



  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: lookup and change value in 1 formula

    To get it to work with the decimal marker used in Ireland, I changed the , to . With your local settings it should be fine.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 10-01-2019, 02:05 AM
  2. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  3. Lookup formula to detect change
    By sharp_shooter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-22-2014, 12:52 PM
  4. change lookup formula
    By dougers1 in forum Excel General
    Replies: 5
    Last Post: 09-21-2014, 02:36 PM
  5. Change lookup formula to VB
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 01-03-2014, 08:26 PM
  6. Find/Replace macro with lookup table of change from to change to
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-19-2013, 07:37 AM
  7. LOOKUP formula change
    By donyc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2012, 07:26 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1