+ Reply to Thread
Results 1 to 4 of 4

Keep leading zero when converting to text to use for vlookup formulas

  1. #1
    Registered User
    Join Date
    05-14-2012
    Location
    Spring Valley, NY
    MS-Off Ver
    Excel 2010
    Posts
    27

    Keep leading zero when converting to text to use for vlookup formulas

    Hi, I am trying to do a vlookup by UPC code where the lookup file of the UPC codes has the info coded the cells to display 12 digits

    Example: UPC code in the formula bar shows: 23063601212 but when you look at the cell it has a leading zero in front of the number: 023063601212

    The problem is when I convert this cell from a number to text it doesn't put the zero in front of the number and when I do a vlookup to get the cost for this UPC code it doesn't find it.

    how do I keep the leading zero in the cell where the numbers don't have 12 digits into a text so I can properly do my vlookups.

    I am attaching the spreadsheets so you can see what I mean.

    File#1 - Named: UPC Code with lookup - this is the file I am trying to bring in the current cost so I can compare current pricing to my previous pricing.
    File#2 - Named: C Master Inventory file 2018 - this is the file I am trying to pull the current cost from but the UPC code is not converting to text and leaving the leading zero in there when it's missing a digit.

    I would appreciate anyone's help who can assist me in being able to pull the data from the master file so I can pull in my current cost into the UPC code with vlookup file.

    Thank you!

    YR
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Keep leading zero when converting to text to use for vlookup formulas

    Please try UPC code A2

    =VLOOKUP(--A2,'[RC Master Inventory File 2018.xlsx]product_inventory-4'!$A:$G,7,FALSE)

    the column A in RC Master Inventory is Number --A2 will convert text to number

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

    Re: Keep leading zero when converting to text to use for vlookup formulas

    ... and bear in mind that a SAMPLE sheet with 10-20 carefully selected rows is HUGELY preferred over 10000+ rows. They make manual checking virtually impossible.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    05-14-2012
    Location
    Spring Valley, NY
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Keep leading zero when converting to text to use for vlookup formulas

    Bo, thank you for the simple resolution. It works!!

    Thank you for saving me time and reducing stress, you should be blessed for sharing your knowledge and your willingness to help people

    Thank you again,

    YR

+ 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. [SOLVED] Keep leading zeros through text referencing formulas
    By kx450f201 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2014, 11:37 AM
  2. VBA for converting formulas to text
    By hk4kim in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2013, 08:24 PM
  3. Preserve leading zeros in a varying length number when converting it to text
    By Paulymon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2013, 12:48 AM
  4. Converting text to number, dropping leading zero
    By MichelleW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2009, 04:31 PM
  5. Converting selective formulas to text
    By josnah in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2008, 11:09 AM
  6. converting concatenated text strings into formulas
    By z.entropic in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-05-2006, 01:20 PM
  7. [SOLVED] converting text to formulas
    By Scot B in forum Excel General
    Replies: 1
    Last Post: 03-01-2005, 09:06 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