| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing leading zeros from a group of numbers
I have a report that is imported from another source. A column has numbers
showing as 00047, formatted as "general". I need to use those numbers in a vlookup formula - and the lookup table has the numbers without the leading zeros. So right now, nothing is pulling. Is there a way to get rid of those leading zeros - either by formatting or with a formula - so I can avoid re-entering them all by hand (there are about 1500 of them). I use Excel 2000 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing leading zeros from a group of numbers
In an un-used cell, enter 1. Copy this cell. Select all the cells that you
want to fix. Paste/Special with the multiply button checked. -- Gary''s Student "MAC253" wrote: I have a report that is imported from another source. A column has numbers showing as 00047, formatted as "general". I need to use those numbers in a vlookup formula - and the lookup table has the numbers without the leading zeros. So right now, nothing is pulling. Is there a way to get rid of those leading zeros - either by formatting or with a formula - so I can avoid re-entering them all by hand (there are about 1500 of them). I use Excel 2000 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing leading zeros from a group of numbers
One way is to use a helper column and enter =A1*1 and copy down......
Then Copy PasteSpecial Values on that column to eliminate the formulas Vaya con Dios, Chuck, CABGx3 "MAC253" wrote: I have a report that is imported from another source. A column has numbers showing as 00047, formatted as "general". I need to use those numbers in a vlookup formula - and the lookup table has the numbers without the leading zeros. So right now, nothing is pulling. Is there a way to get rid of those leading zeros - either by formatting or with a formula - so I can avoid re-entering them all by hand (there are about 1500 of them). I use Excel 2000 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Removing leading zeros from a group of numbers
If the values all look like numbers, you could change your =vlookup() formula to
look like: =vlookup(--a2,sheet2!a:b,2,false) The -- stuff will coerce the text number to a real number. -"00047" becomes -47 --"00047" becomes the opposite of -47 or +47 (= 47). MAC253 wrote: I have a report that is imported from another source. A column has numbers showing as 00047, formatted as "general". I need to use those numbers in a vlookup formula - and the lookup table has the numbers without the leading zeros. So right now, nothing is pulling. Is there a way to get rid of those leading zeros - either by formatting or with a formula - so I can avoid re-entering them all by hand (there are about 1500 of them). I use Excel 2000 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| retain leading zeros when importing | Excel Discussion (Misc queries) | |||
| removing leading zeros in numeric fields | Excel Discussion (Misc queries) | |||
| Social Security Numbers & Leading Zeros | Excel Discussion (Misc queries) | |||
| How do I stop excel removing zeros? | Excel Discussion (Misc queries) | |||
| Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Linear Mode
