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 ...
... 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.A1 contains 999
B1 contains Z
in C1 put =concatenate(A1,B1) (giving 999Z)
then drag down as required.
How can I make this work so that the contents of one column are added to the contents of another?
Malcolm
Just put in the first formula in B (or C, whatever) then copy it down the rest of the column.A B VCTAHBC3 =A2&".jpg" VCTAHSP =A3&".jpg" FAIP481516 =A4&".jpg" etc
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
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!Originally Posted by Ron Coderre
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
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Works a treat - you are a rocket scientist
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks