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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,001
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,001
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,001
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,805
Messages
5,638,468
Members
417,026
Latest member
UDK

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