# dropping the leading zero

1. ## dropping the leading zero

I am having issues with leading zeros dropping off when I copy/paste into cells. I do Vlookup and convert my columns to text to be sure that I will get a return on my lookup. The numbers I use are manufacturer or vendor part numbers such as 02273480. In some cases, when I add this number into a cell, the leading zero is gone and I will not get a match. I know that if I add ' to the cell first, the zero stays but when I am pasting 70-100,000 lines at a time, that isnt an option. any help would be great.

Thanks

Kelly

2. ## Re: dropping the leading zero

Hi Kelly,

Try using this formula... =TEXT(A1,"00000000")

The number of zeros inside the quote will depend how many you need...

Hope this helps...

- Dennis

re-post: are you comparing numbers to numbers? If so, the format won't matter... but if you are comparing number and text will be a problem...

Why don't you attach a sample workbook for us to look at...

3. ## Re: dropping the leading zero

If you do the "Find/replace" and remove the periods and replace with nothing, the leading zero drops.

test excel.xlsx

4. ## Re: dropping the leading zero

If you add you're desired result in the sheet, you get an better anwer.

5. ## Re: dropping the leading zero

I need the leading zero to stay in place when I remove the periods or dashes.

6. ## Re: dropping the leading zero

Like the solution in #2.

7. ## Re: dropping the leading zero

sorry, the first upload was the test sheet I used and the zeros were already removed. here is the sheet I want to remove the periods and keep the leading zeros.

22test excel.xlsx

8. ## Re: dropping the leading zero

Hi Kelly,

Try this formula... =TEXT(SUBSTITUTE(A1,".",""),"000000000000")

re-post: hmmm, what to do with row 14? do you only want 1 leading zero?

re-post2: just use this... =SUBSTITUTE(A1,".","")

9. ## Re: dropping the leading zero

I need the numbers to remain as they are but without the periods and dashs. I hope that makes sense

10. ## Re: dropping the leading zero

Do a 2 step process, use the the SUBSTITUTE formula first with the "." then with "-"

##### Users Browsing this Thread

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

#### 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