Having issues adding “0” to concatenation

[Wintchase]

Hi, I am trying to concatenate cells.. I have gotten one leading zero taken care of, but when I have two leading zeros I keep getting an error? Can someone look at this and let me know abt I’m messing up?

=CONCATENATE(LEFT(A2,5),IF(LEN(H2=3,”0”&H2,H2),IF(LEN(I2)=3,”0”&I2,I2, IF(AND(LEN (H2)=2,”0”&H2,H2),IF(LEN(I2)=3,”0”&I2,I2))

[Roel Jongman]

I think You are simply missing a closing brackey after LEN(H2=3

Please Login or Register  to view this content.

[Pete_UK]

Do you want to ensure that H2 and I2 are both 4 digits in length when concatenated? If so you can do this:

=LEFT(A2,5)&TEXT(H2,"0000")&TEXT(I2,"0000")

Your formula seems to be trying to concatenate them twice.

Hope this helps.

Pete

[Wintchase]

Do you want to ensure that H2 and I2 are both 4 digits in length when concatenated? If so you can do this:

=LEFT(A2,5)&TEXT(H2,"0000")&TEXT(I2,"0000")

Your formula seems to be trying to concatenate them twice.

Hope this helps.

Pete

Hi! That helped... but now the numbers shift left if I have the second set of numbers with three numbers, and the first set four numbers.. is there a way to freeze the positions?

[Pete_UK]

I don't understand what this means - perhaps you could explain what you are trying to do in a bit more detail.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[Wintchase]

Hi, I concatenated two columns with leading zeros, but if the first column has four numbers, and the second column has three numbers and 0, all the numbers shift right and I end up with a zero leading the first four numbers? Does anyone know how to keep the numerals in their proper positions?

=LEFT(A2,5)&TEXT(H2,"0000")&TEXT(I2,"0000")

[Sam Capricci]

I believe to get a better formula it would be helpful for you to do what Pete recommended in post #5 and upload a sample workbook.
What you were given was the best guess based on the formula you showed in post #1 but your descriptions in your posts, I believe, aren't sufficient enough to give you a formula.
AND, if you upload a sample workbook please make sure you put in sufficient samples AND desired results.

[Wintchase]

Weird... I when I do this formula at work, I get a result like this ABCDE00123478...? When I use my computer in my hotel, it
works fine.. Any clues? Thanks so much for your patience, and help! I'm a newb..

using this formula: =(LEFT(A2,5)&TEXT(C2,"0000")&TEXT(D2,"0000"))


ABCDE1111111 ABCDE12345678 1234 5678
ABCDE1111112 ABCDE12340678 1234 678
ABCDE1111113 ABCDE12340078 1234 0078