Concatenating columns with formatting

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

HI

I have a column of numbers

123 and then another column that has 3 digits.
BUT That column we format using 000000 so that it displays
like 000234 so our spreadsheet is like this

123 000234

I would like to concatenate the two.
When we do it does 123234
and we want 123000234
Can you help me?
Thanks in advance.
Ken
 
Hi Ken...........

If you can tolerate the second column being formatted as TEXT, it will
concatenate as you want........

Vaya con Dios,
Chuck, CABGx3
 
I'm sorry I was not clearer.
We already use a custom formatting 000000
that adds 3 zeros to the number. So
I don't understand how your answer could work.
Sincerely,
Ken
 
Hi Ken............

Another way........(all on one line)

=IF(LEN(B1)=1,A1&"00000"&B1,IF(LEN(B1)=2,A1&"0000"&B1,IF(LEN(B1)=3,A1&"000"&
B1,IF(LEN(B1)=4,A1&"00"&B1,IF(LEN(B1)=5,A1&"0"&B1,IF(LEN(B1)=6,A1&B1,"")))))
)

Vaya con Dios,
Chuck, CABGx3
 
Ken

CLR's answer would work if instead of the custom format
000000 you formated the column as text and when inputing
the number type all leading zeroes... you can avoid that
by using:


=A1&TEXT(B1,"000000")

No need to format as text the other column but this result
*will* be text... if you want numeric properties on your
concatenated result the use:

=--(A1&TEXT(B1,"000000"))

cheers
Juan
 
How cool that is Juan...........another one for my goodie-box stash,

Thanks
Vaya con Dios,
Chuck, CABGx3
 
-----Original Message-----
Ken

CLR's answer would work if instead of the custom format
000000 you formated the column as text and when inputing
the number type all leading zeroes... you can avoid that
by using:


=A1&TEXT(B1,"000000")

No need to format as text the other column but this result
*will* be text... if you want numeric properties on your
concatenated result the use:

=--(A1&TEXT(B1,"000000"))

cheers
Juan

.
Click to expand...
 
Back
Top