Concatenating with numbers

Kurganscott

New Member
Joined
Nov 10, 2005
Messages
4
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. :rolleyes:

Anybody? Bueller? :biggrin: Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Welcome to the board.

Try:

=TEXT(CONCATENATE(A1,B1,C1,D1,E1),"00000")
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
(There is a typo in Andrew's formula. Should be:)

=A1&TEXT(B1,"0000")&C1&TEXT(D1,"00")&TEXT(E1,"000")
 
Upvote 0
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! :biggrin:

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...
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top