# Concatenating with numbers

#### Kurganscott

##### New Member
I have need to combine 5 columns into one field. Using the CONCATENATE function accomplishes this but in fields that begin with a zero, the formula (or possibly the format of the target cell, although I have tried variations with no success) removes the zeros leaving on the number at the end. The needed final result is a coding field for another program that requires those zeros.

Anybody? Bueller? Thanks!

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Post a sample of your data and the result you want.

Welcome to the board.

Try:

=TEXT(CONCATENATE(A1,B1,C1,D1,E1),"00000")

Sample... Five Fields. I tried the above (thanks for the tip and welcome!) but to no avail. I appreciate your quick responses all!

D 0001 1 00 001

Scott

I assume those are the values in those cells (as opposed to the second cell having the value of 1 but being formatted as 0000 to display 0001).

What is the desired result?

D0001100001 would be the desired result (11 characters from 5 fields)

Field 1 = D
Field 2 = 0001
Field 3 = 1
Field 4 = 00
Field 5 = 001

Kurganscott said:
D0001100001 would be the desired result (11 characters from 5 fields)

Field 1 = D
Field 2 = 0001
Field 3 = 1
Field 4 = 00
Field 5 = 001

Like this?

=A1&TEXT(B1,"0000")&C1&TEST(D1,"00")&TEXT(E1,"000")

(There is a typo in Andrew's formula. Should be

=A1&TEXT(B1,"0000")&C1&TEXT(D1,"00")&TEXT(E1,"000")

Try:

=(a1&TEXT(b1,"0000")&c1&TEXT(d1,"00")&TEXT(e1,"000"))

GOD love you guys! This is how it ended up, worked PERFECTLY!

=B2&TEXT(C2,"0000")&D2&TEXT(E2,"00")&TEXT(F2,"000")

Thanks a bunch all, if you are ever in Louisville, Ky look me up, we'll do lunch!

What a great website, I had no idea. Firmly placed in my favorites now however. I have been hacking my way through excel for years now. AHHH the power of the internet...

Replies
4
Views
177
Replies
5
Views
424
Replies
4
Views
229
Replies
4
Views
653
Replies
1
Views
316

1,216,372
Messages
6,130,223
Members
449,567
Latest member
ashsweety

### 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.

### Which adblocker are you using?

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

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