Concatenating but keeping the leading zeros

Formulation

New Member
Joined
Aug 26, 2016
Messages
25
I'm trying to concatenate several columns, one of which contains leading zeros in some of the cells.

Example below: Row two is correct (PN # Column)
Row 1 is not bringing over the "00" in "003"
I have the length column set to display as 003 but concatenate is removing the two 0's.


PN #LengthFeetOther Parts
C1-LCLCBD3OM2APLDC3M

<tbody>
</tbody>
003MC1-LCLCBD3OM2APLDC
C1-LCLCBD3OM2APLDC270F

<tbody>
</tbody>
270F
C1-LCLCBD3OM2APLDC

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
If 003 is in cell B2, you can try something like

Code:
=CONCATENATE(A2;TEXT(B2;"000"))
 

Formulation

New Member
Joined
Aug 26, 2016
Messages
25
If 003 is in cell B2, you can try something like

Code:
=CONCATENATE(A2;TEXT(B2;"000"))

Thanks. My original formula is:
Code:
=CONCATENATE(F7, "-",K7,M7,AA7,Q7,O7,W7,U7,Y7,I7,H7)
I7 in the formula = 003
Sometimes it may be 012, which is also only displaying as 12 after concatenating.

How would I use your code but include the other cells including the dash(-) after F7?


I've tried a few methods I found on google but they either won't work or are related to adding a static number of 0's as a prefix.
 
Last edited:

Formulation

New Member
Joined
Aug 26, 2016
Messages
25
I tried something new and it seems to be working. I suppose I'll use this unless someone has a reason why I shouldn't.
Code:
=CONCATENATE(F8, "-",K8,M8,AA8,Q8,O8,W8,U8,Y8,[B]TEXT(I8,"0##")[/B],H8)
It's concatenating 001, 011, and 111 correctly.
 

Formulation

New Member
Joined
Aug 26, 2016
Messages
25
Actually ended up using something similar to your formula and it worked. Thanks
Code:
=CONCATENATE(F7, "-",K7,M7,AA7,Q7,O7,W7,U7,Y7,TEXT(I7,"000"),H7)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top