How can I create a 3rd column that will equal (not equal as in sum)the contents of the first to colu

Posted by S. Grassy on February 15, 2001 10:08 AM

I'm looking for a way to create a 3rd column that will equal the contents in the first two columns, not the sum of the first two columns but the content.

example
col A = 01 and col b = 121. I would like col C to read 01121, with no spaces. Everything I have tried, always drops the 0 off of the col. c.

Can anyone help?

Posted by Michelle on February 15, 2001 10:43 AM

There a couple ways to do this. For instance:

=A1&B1

=concatenate(A1,B1)

I think this should help.

Posted by Aladin Akyurek on February 15, 2001 11:10 AM

First Format A1 and B1 as Text via Format Cells, the use Michelle's suggestion or the equivalent formula

C1 =A1&B1

Aladin

Posted by S. Grassy on February 16, 2001 10:05 AM

Thank you, I believe I'm getting closer. I still have a problem yet. In order to get 2 characters in column a and 3 charaters in column b. I had to format cells custom, I need to have a total of 5 charaters in column c. If I then format text the preceding zeros drop off again. Any suggestions.
example
My file transfers into excel and looks like this.
A B
9 13
9 15

I format custom and it looks like this:
a b
09 013
09 015

when I then format cells text, the zeros drop off.
Any suggestions.
S.

Posted by Aladin Akyurek on February 16, 2001 12:57 PM

You don't need any formatting: keep the values as imported.
And enter in

C1 =IF(LEN(A1)=1,"0"&A1,A1)&IF(LEN(B2)=2,"0"&B1,B1)

Copy down this formula as far as needed.

Aladin

Posted by Aladin Akyurek on February 16, 2001 1:14 PM

Make that B2 B1!

Posted by Mark W. on February 16, 2001 4:21 PM

Don't format the cells in columns A:B. Use

=TEXT(A1,"00")&TEXT(B1,"000")

Posted by Aladin Akyurek on February 17, 2001 12:27 AM

Yep. This is much better. Dropped it just because it didn't work on my office system at school, which is not English!

Posted by S. Grassy on February 19, 2001 10:47 AM

Don't format the cells in columns A:B. Use

Thank you Both, This is exactly what I needed!! The only problem being is when I transfer this file to my mapping software. It doesn't pull the entire file. Go figure. either of you happen to be a MapInfo professional. Just kidding!
Thanks again

Posted by s. grassy on February 21, 2001 9:39 AM

Is there someway to loss the formula in my cells, but still hold the 5 space text, that we created with the =text formula, you gave me? The software that I'm transfering to will not except the formula.

Posted by Aladin Akyurek on February 21, 2001 12:33 PM

Select and copy the cells in which you created the strings of length 5. Go to the first cell of the next empty column, and activate the option Edit,Paste Special and choose Values on 'Paste Special' window.

Aladin