Concatenating columns

**ArtisanInternet** · 11-12-2008, 01:16 PM

I have to add the extension .jpg to a very long column of reference numbers to turn them into filenames so that ...

VCTAHBC3
VCTAHSP
FAIP481516
etc.

... becomes ...

VCTAHBC3.jpg
VCTAHSP.jpg
FAIP481516.jpg
etc.

I've seen the following solution in another thread ...

A1 contains 999
B1 contains Z

in C1 put =concatenate(A1,B1) (giving 999Z)

then drag down as required.

... but being thick I don't know what "then drag down as required" refers to - does it mean drag C1 down so it includes C1 to C3? I've tried that and I only get the result for the first row; "VCTAHBC3.jpg". I've also tried dragging A1 down to A3 but got the same result.

How can I make this work so that the contents of one column are added to the contents of another?

Malcolm

**JBeaucaire** · 11-12-2008, 01:24 PM

Please Login or Register  to view this content.

Just put in the first formula in B (or C, whatever) then copy it down the rest of the column.

**Ron Coderre** · 11-12-2008, 02:25 PM

Perhaps this will work for you....

Select the cells to have the file extension appended.

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: @".jpg"
Click [OK]

Note: The VALUE of the cells will not change, but they will display .jpg
after the contents.

Example:
A1 contains Alpha
but displays Alpha.jpg

Is that something you can work with?

**ArtisanInternet** · 11-13-2008, 05:59 AM

Originally Posted by Ron Coderre

Perhaps this will work for you....

Select the cells to have the file extension appended.

From the Excel Main Menu:
<format><cells><number tab>
Category: Custom
Type: @".jpg"
Click [OK]

Note: The VALUE of the cells will not change, but they will display .jpg
after the contents.

Example:
A1 contains Alpha
but displays Alpha.jpg

Is that something you can work with?

I didn't think it would but it did! As you say, the .jpg extension isn't displayed in the VALUE initially, but it does display there after I've closed and re-opened the file. I was therefore able to import the data into the ecommerce site I'm working on - thank you!

The next problem is that I now need to add some characters before and after some text in a cell. HTML in plain text fields in the software is enclosed within the tags !!< and >!!

Example - This is <b>bold</b> text has to be changed to !!<This is <b>bold</b> text>!!

I can now add something to the end of the text, thanks to your instructions, but is it possible to add something to the beginning?

Malcolm

**JBeaucaire** · 11-13-2008, 06:39 AM

You do this using the same solution offered above. A CUSTOM FORMAT in a cell can have additions in front or behind the raw data. The format can be: "!!"@".jpg"

Basically anything inside double quotes gets added where you put it. The @ is your data.