+ Reply to Thread
Results 1 to 11 of 11

Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

  1. #1
    Registered User
    Join Date
    05-01-2019
    Location
    Milton Keynes
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Hello
    My apologies for asking. I feel so stupid but I suppose I have to learn somewhere. Any help would be excellent

    I have cells with leading zeros

    000004 Hempstead Valley #N/A
    000006 Dartford #N/A
    000007 Carmarthen #N/A
    000009 Ballymena #N/A
    000010 Boston #N/A
    000012 Wisbech #N/A
    000013 Lichfield #N/A
    000014 Stamford #N/A
    000015 Shepherds Bush #N/A
    000016 Braehead #N/A
    000020 Balham #N/A

    My source data is

    000004
    000004
    000006
    000006
    000007
    000007
    000010
    000010
    000012
    000012
    000013
    000013
    000014
    000014
    000015
    000015
    000020
    000020

    My vlookup is =VLOOKUP(A1,Source!A:A,1,FALSE)

    But it comes back n/a
    I have tried to =TRIM(A1) etc but I lose the leading zeros.

    I have attached a sample file
    Attached Files Attached Files
    Last edited by UFBEE1970; 05-02-2019 at 03:47 AM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    No Sample attached
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    05-01-2019
    Location
    Milton Keynes
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Quote Originally Posted by TMS View Post
    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Appreciate the swift response.

  6. #6
    Registered User
    Join Date
    05-01-2019
    Location
    Milton Keynes
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Quote Originally Posted by mehmetcik View Post
    No Sample attached
    Apologies, thought I attached the file

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Apologies, thought I attached the file
    No, and you still haven't.

    Does the solution offered work for you?

  8. #8
    Registered User
    Join Date
    05-01-2019
    Location
    Milton Keynes
    MS-Off Ver
    Microsoft 365
    Posts
    4

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Responded to last message from ipad.

    attached file
    Solution did not work for me

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Try:
    =VLOOKUP(A1+0,Source!A:A,1,FALSE)
    Quang PT

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    Perhaps extending bebo021999's formula?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Vlookup - keeping the leading zeros in a cell to allow the vlookup to work

    On Export C1 you can try:
    Please Login or Register  to view this content.
    However, it will only return the number you are looking up (if it's present in the list) or #N/A. I don't know why you want to do that.

    If you use:
    Please Login or Register  to view this content.
    in B1 on Source sheet, it will return the corresponding city (or #N/A) from Export.

    Note, these are both array formulas
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ben Van Johnson

+ 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] powery query issue keeping leading zeros
    By JEAN1972 in forum Excel General
    Replies: 2
    Last Post: 11-25-2017, 01:38 AM
  2. Remove Leading zeros keeping hidden apostrophe as is
    By wachao in forum Excel General
    Replies: 16
    Last Post: 03-19-2014, 07:47 AM
  3. VLookup and leading zeros
    By zloep in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-31-2013, 11:30 AM
  4. Replies: 6
    Last Post: 01-11-2012, 01:48 PM
  5. Excel 2007 : Keeping leading zeros
    By 2007novice in forum Excel General
    Replies: 2
    Last Post: 06-07-2011, 04:22 PM
  6. Keeping Leading Zeros
    By teddybouch in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2008, 02:17 PM
  7. [SOLVED] How to copy a number into a text cell, keeping leading zeros?
    By Basher Bates in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-15-2006, 08:10 AM

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