Concatenating numbers as text and retaining text format

Column C has numbers downloaded in text format, i.e. each cell has a green smart tag and a value might be 000 or 001. In Column A, when I refer to column C in a concatenation, the 001 is showing in column A as 1. So the number as text is being converted to a number format after the concatenation is applied. Rather than having this, I would like to retain the values in Column C as is, i.e. 000 should stay as 000 and 001 should remain 001 when they appear in column A.

I forgot how to do this.


Initially I tried to start by retaining the values in column C as follows.

Please Login or Register  to view this content.

Hi, how does your concatenation looks like?
If you concatenate a text with col C, value in col C shall remain 001 e.g. A001.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Originally Posted by millz

When you select the cell in column C, what's the value in the formula bar? Is it "1" or "001"?

If it's "1", you can concatenate using

Please Login or Register  to view this content.

The cell has 001 with a green smart tag, and the formula bar also has 001. But for some reason, when I concatenate, it converts the 001 to a 1.

Originally Posted by alvin-chung

Hi, how does your concatenation looks like?
If you concatenate a text with col C, value in col C shall remain 001 e.g. A001.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Here is my code:

Please Login or Register  to view this content.

I checked the values in C and they are displayed AND stored as 001, 002, etc. So it looks like they are already in text format. The For loop does the concatenation. The leading zeros are dropped of, and the hyphen is being read as a subtraction sign and a subtraction is being done.

How do I retain the leading zeros so that I get something like 1813-001 instead of 1812?

Originally Posted by millz

That's because you are setting it into a formula.

Try changing to this:

Please Login or Register  to view this content.

Edit: If you must use a formula, try this

Please Login or Register  to view this content.

Thank you very much. It worked. I clicked Add reputation for you.

I also tried to understand the formula way of doing it, but I keep getting lost with all those quotations and ampersands. Thank you for your help.