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

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

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

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

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

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

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