Leading Zeroes Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1326795%22%20slang%3D%22en-US%22%3ELeading%20Zeroes%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326795%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20with%20unique%20IDs%20in%20which%20i%20am%20performing%20VLookups%20to%20compare%20values%20between%20difference%20tabs.%26nbsp%3B%20%26nbsp%3BIt%20is%20extremely%20important%20the%20values%20in%20each%20cell%20remain%20exactly%20as%20they%20were%20found%20in%20the%20source%20system%20when%20i%20convert%20them%20to%20number.%26nbsp%3B%20%26nbsp%3BI%20have%20scoured%20online%20and%20shockingly%20i%20cannot%20find%20ANY%20advice%20that%20meets%20the%20following%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20RETAINS%20the%20leading%20zeroes%20for%20values%20that%20contain%20them%20(not%20every%20cell%20has%20a%20leading%20zero%2C%20and%20some%20have%20multiples%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Converts%20the%20value%20to%20number%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Does%20not%20alter%20or%20change%20ANY%20other%20values%20in%20the%20cell%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20change%20the%20format%20to%20text%2C%20but%20then%20Vlookup%20doesn't%20work.%26nbsp%3B%20I%20tried%20looking%20at%20custom%20format%20but%20i%20don't%20want%20my%20cells%20to%20have%20a%20predefined%20number%20of%20values%20either.%26nbsp%3B%20Some%20cells%20will%20only%20be%204%20digits%20long%2C%20while%20others%20may%20have%209.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%26nbsp%3B%20I%20am%20incredibly%20frustrated%20as%20to%20how%20difficult%20excel%20is%20making%20this%20basic%20task!%26nbsp%3B%20I%20would%20be%20eternally%20grateful%20for%20any%20helpful%20assistance%20anyone%20may%20provide.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Brad%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1326795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1326873%22%20slang%3D%22en-US%22%3ERe%3A%20Leading%20Zeroes%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631703%22%20target%3D%22_blank%22%3E%40Ebiru2387%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20upload%20a%20workbook%20showing%20the%20problem%3F%26nbsp%3B%20I%20tried%20it%2C%20and%20I%20think%20it's%20working%20for%20me%2C%20so%20maybe%20I'm%20not%20duplicating%20the%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi all,

 

I am working with unique IDs in which i am performing VLookups to compare values between difference tabs.   It is extremely important the values in each cell remain exactly as they were found in the source system when i convert them to number.   I have scoured online and shockingly i cannot find ANY advice that meets the following criteria.

 

- RETAINS the leading zeroes for values that contain them (not every cell has a leading zero, and some have multiples

 

- Converts the value to number

 

- Does not alter or change ANY other values in the cell

 

 

I have tried to change the format to text, but then Vlookup doesn't work.  I tried looking at custom format but i don't want my cells to have a predefined number of values either.  Some cells will only be 4 digits long, while others may have 9.

 

Can anyone help?  I am incredibly frustrated as to how difficult excel is making this basic task!  I would be eternally grateful for any helpful assistance anyone may provide.

 

Kind Regards,

 

- Brad 

1 Reply
Highlighted

@Ebiru2387 

Can you upload a workbook showing the problem?  I tried it, and I think it's working for me, so maybe I'm not duplicating the problem.