Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Add the same characters to 4000  strings in Excel

Posted on 2011-02-23
Medium Priority
Last Modified: 2012-06-27
Hi -
This is probably a simple question to someone who uses Excel a lot.

Here's the situation...
I'm using Excel2010.  I have a spreadsheet with about 4000 rows of serial numbers, short descriptions, etc.   One of the columns is image names but without the ".jpg" file extension.  I need to add ".jpg" to all 4000 image names in that column.  How is this done besides typing it in one image at a time?

If I use Find and Replace then I'll have to do it 36 times to match up with the image names that end in all the different alpha numeric characters. Isn't there a way to add the file extension (or any set of characters) to every string in a column?

TIA your Excel expertise
Question by:dzash2000
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34960733
I would add another column, and let's say taht your image name is in column E
then in the next column i would place a formula like this:   =E2 & ".jpg"  
for example (E1 beeing the header), i would copy that formula to every other rows.

You can simply hide column E to not have the same information twice, or, copy column F over column E doing a "Paste VALUE", and then get rid of column F
LVL 11

Accepted Solution

Runrigger earned 1000 total points
ID: 34960735
=CONCATENATE(<<image cell ref>>,".jpg")

then copy it down, so if the image name is in column C, assuming a column header;

put this in column D

LVL 45

Expert Comment

ID: 34960752



and copy down to the end of the data in column A

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Closing Comment

ID: 34961206
Runrigger -
Excellent.  A clear, succinct explanation and example.
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34961295
dzash: Our 3 solutions (Runrigger, Patrickab and mine) were working.  They all do the same thing.  Mine is exactly the same than Patrickab except that i've specified how to remove the column with only the image name.

CONCATENATE and the operator "&" are doing exactly the same job.

Next time, it would be nice to split the points between people who lead you to your answer.
Here's some info about it: http://www.experts-exchange.com/help.jsp#hs=8&hi=100)
LVL 11

Expert Comment

ID: 34961409
Christian, my participation on EE is about education not only for the submitter, but for myself also, points are secondary and if you want all 250 points, I for one would be very happy for the author to re-open the question and award them all to you, it is their choice to make not yours!

I am going to assume, based on the author's comments, that my suggestion lead to an appropiate solution for their needs!

LVL 45

Expert Comment

ID: 34961496

Don't worry, I am not expecting any points. Others provided good answers before me.

LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34961641
dzash2000: I'm not asking you to reopen this question to reassign the points too.  It was just for your information, to split the points fairly between people who have helped you.

You might not be aware of that, but Experts-Exchange give to their experts the opportunity to have free access to the service if they reach XXXX Points per month (i think it's 3K or 4K points, i'm not sure).  I honnestly don't care about that: just like i said to patrickab, i'm paying my subscription to have peace of mind.

Even if experts are not doing this for points, by not splitting points, you might offend some and they might not answer you next time that you ask a question.

So think about it for your future questions...

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question