Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to copy a number into a text cell, keeping leading zeros?
In order to standardise an index reference for a LOOKUP table, I need to
CONCATENATE cells from three columns, which could be (typically) as follows:- B, 010, 030 or even A, 000, 040. This would give a concatenation of B010030, etc.. The data has been standardised into this form to allow sorting within a reference table and I then need to extract cells within that table for use in my spreadsheet, using the LOOKUP functions. This works fine if I input '000' as text but if I try to convert a 3-digit numerical cell to text the leading zeros are dropped, resulting in B1030 or A040, using the above examples. Does anybody have a workaround for this problem? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to copy a number into a text cell, keeping leading zeros?
Hi,
You could use the TEXT() function to maintain leading zeros. =TEXT(6,"000") would give you 006 Cheers Andy Basher Bates wrote: In order to standardise an index reference for a LOOKUP table, I need to CONCATENATE cells from three columns, which could be (typically) as follows:- B, 010, 030 or even A, 000, 040. This would give a concatenation of B010030, etc.. The data has been standardised into this form to allow sorting within a reference table and I then need to extract cells within that table for use in my spreadsheet, using the LOOKUP functions. This works fine if I input '000' as text but if I try to convert a 3-digit numerical cell to text the leading zeros are dropped, resulting in B1030 or A040, using the above examples. Does anybody have a workaround for this problem? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to copy a number into a text cell, keeping leading zeros?
just testing how to reply.
-- cityboyjerry "Basher Bates" wrote: In order to standardise an index reference for a LOOKUP table, I need to CONCATENATE cells from three columns, which could be (typically) as follows:- B, 010, 030 or even A, 000, 040. This would give a concatenation of B010030, etc.. The data has been standardised into this form to allow sorting within a reference table and I then need to extract cells within that table for use in my spreadsheet, using the LOOKUP functions. This works fine if I input '000' as text but if I try to convert a 3-digit numerical cell to text the leading zeros are dropped, resulting in B1030 or A040, using the above examples. Does anybody have a workaround for this problem? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count the number of text frequencies and copy to other cell | Excel Worksheet Functions | |||
On click, copy text into another cell - XL2K | Excel Worksheet Functions | |||
zero supress leading zeros when chg format from text to number | Excel Worksheet Functions | |||
Defining a number in a cell by text then subtracting it by the tex | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |