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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
Welcome to the board.

Try:

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

Kurganscott

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365

ADVERTISEMENT

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?
 

Kurganscott

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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")
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
(There is a typo in Andrew's formula. Should be:)

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

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

Kurganscott

New Member
Joined
Nov 10, 2005
Messages
4
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,525
Messages
5,837,865
Members
430,519
Latest member
abtg1

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