Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
I have a column of reference numbers, there should be 6 numbers in
each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
Try
IF(B20,TEXT(B2,"000000"),"") Mike "Box666" wrote: I have a column of reference numbers, there should be 6 numbers in each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
On 31 Mar, 13:39, Mike H wrote:
Try IF(B20,TEXT(B2,"000000"),"") Mike "Box666" wrote: I have a column of reference numbers, there should be 6 numbers in each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. *If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks- Hide quoted text - - Show quoted text - Perfect, thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
Hi Box666
Try this =IF(B2<1," "," ")&TEXT(B2,"000000") Regards Cimjet "Box666" wrote in message ... I have a column of reference numbers, there should be 6 numbers in each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
=IF(B2="","",TEXT(B2,"000000"))
-- David Biddulph "Box666" wrote in message ... I have a column of reference numbers, there should be 6 numbers in each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
I'm confused. What does =IF(B2<1," "," ")&... do for us? Doesn't that
merely concatenate a space at the beginning of the string, regardless of the value of B2? -- David Biddulph "Cimjet" wrote in message ... Hi Box666 Try this =IF(B2<1," "," ")&TEXT(B2,"000000") Regards Cimjet "Box666" wrote in message ... I have a column of reference numbers, there should be 6 numbers in each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text format and missing value.
Your wright, not my day, posting to fast and not checking what i'm writting.
Same for the other post, going back to bed!!! Thanks for the correction Regards Cimjet "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... I'm confused. What does =IF(B2<1," "," ")&... do for us? Doesn't that merely concatenate a space at the beginning of the string, regardless of the value of B2? -- David Biddulph "Cimjet" wrote in message ... Hi Box666 Try this =IF(B2<1," "," ")&TEXT(B2,"000000") Regards Cimjet "Box666" wrote in message ... I have a column of reference numbers, there should be 6 numbers in each reference, sometimes the leading number is a zero. I am using =TEXT(B2,"000000") to show all of the digits. Sometimes the reference column is blank and if that is the case then i do not want to see 000000 but rather have it show as blank. If it was still a number column i could have used =IF(B21;B2;" ") Could somebody show me the best way to combine these two items so as to show a 6 digit number in column B unless the original field was blank in which case the new field should also be blank. with thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Office 2K3 Scroolbar Format Control missing a tab. | Excel Discussion (Misc queries) | |||
Missing Text in a List Box | Excel Discussion (Misc queries) | |||
Missing tabs in Format Cells Menu | Excel Worksheet Functions | |||
Missing text in a cell? | Excel Discussion (Misc queries) | |||
Missing format and symbols | Excel Discussion (Misc queries) |