concatenate a text value

[jordanburch]

Hey all,

I have a column of data that is as such
0004354
0034567
5678
4

I need them to be 7 digits with leading 0s IE
0000004

I did the text function but then I need to concatenate it or add a " before and after the number

concatenate(""",A4,"""")

the A4 inside the concatenate only returns "A4" when I want it to return "0000004"

any ideas?

Jordan

 

[Joe4]

Concatenate will combine the actual value of the entry (what you see in the cell editor), not the formatted value.
If you want to maintain the leading zeroes which are only visible because of custom formatting, you need to use the Text function like this:

=concatenate(""",TEXT(A4,"0000000"),"""")

 

[jordanburch]

Concatenate will combine the actual value of the entry (what you see in the cell editor), not the formatted value.
If you want to maintain the leading zeroes which are only visible because of custom formatting, you need to use the Text function like this:

=concatenate(""",TEXT(A4,"0000000"),"""")

thanks for your help. For whatever reason when I click enter it just selects the "0000000" portion and does not allow the formula. Im not sure how to fix that

Jordan

 

[Joe4]

It looks like there may be an error/typo in the original formula you posted, and I copied that.
It appears that you do not have the correct number of " somewhere.
Fix that, and just insert TEXT(A4,"0000000") where you have A4 and I think it should work.

 

[Fluff]

Another option getting rid of all the """" is

=CHAR(34)&TEXT(A4,"0000000")&CHAR(34)

 

[Peter_SSs]

Another option - do it all in the TEXT function.

21 05 04.xlsm
	A	B
4	4354	"0004354"
5	34567	"0034567"
6	5678	"0005678"
7	4	"0000004"
Format Number

Cell Formulas
Range	Formula
B4:B7	B4	=TEXT(A4,"\""0000000\""")

 

[jordanburch]

Another option - do it all in the TEXT function.

21 05 04.xlsm
	A	B
4	4354	"0004354"
5	34567	"0034567"
6	5678	"0005678"
7	4	"0000004"
Format Number

Cell Formulas
Range	Formula
B4:B7	B4	=TEXT(A4,"\""0000000\""")

thanks Peter! looks like i got all the TITANS of the board helping me today!

Jordan

 

[jordanburch]

Another option getting rid of all the """" is

=CHAR(34)&TEXT(A4,"0000000")&CHAR(34)

thanks Fluff! looks like i got all the TITANS of the board helping me today!

Jordan

 

[jordanburch]

It looks like there may be an error/typo in the original formula you posted, and I copied that.
It appears that you do not have the correct number of " somewhere.
Fix that, and just insert TEXT(A4,"0000000") where you have A4 and I think it should work.

thanks Joe! looks like i got all the TITANS of the board helping me today!

Jordan

 

[Fluff]

Glad we could help & thanks for the feedback.