Concatenate

Topic

New Reply

Office 2000
I need to add zeros to create a five digit code (ex. 000234, instead of 234). I created a column that contains 000 formatted as text. My formula is =CONCATENATE(g9,f9). When I tab out of the cell the result is =CONCATENATE(g9,f9) not 000234.
Thanks in advance!

Skip

Reply | Quote

Replies

Check the format of your destination cell, it seems to be text, so what you type is exactly reproduced as is. Format your cell as general, it should return the right code. BTW, as simpler formula is =RIGHT(“00000” & F9, 5) which can deal with codes in column F of 1 to 5 digits and return a string of 5 characters padded on the left with 0’s.

Reply | Quote

Thanks so much. Your suggestion will save me time as well.

Skip

Reply | Quote

Why not just use the Text Function

=TEXT(Cell,”Format”)

For Example if A1 contain 123 then =TEXT(A1,”00000″) produces 00123

If you want 6 digits use “000000”

Reply | Quote

Why not just use the Text Function

=TEXT(Cell,”Format”)

For Example if A1 contain 123 then =TEXT(A1,”00000″) produces 00123

If you want 6 digits use “000000”

Yes that would work as well. I still needed to add a three digit alpha code to the front as well (ex. abc00123). I might not have explained that fully.

Thanks,

Skip

Reply | Quote

=”abc”&TEXT(A1,”00000″)

Steve

Reply | Quote