excel concatenate prefix in comma separated cell

Question

I am horrible with excel. I rarely have to use it but today I do.

I have cells that have images that are comma separated. these cells are in the same column. i need to add a prefix lg_ to each comma separated image in the cell. I have done some reading on concatenate in excel and I am not sure if this is the right method to use. Does anyone have any good tricks/tips to accomplish this?

It currently looks like :

b3f9e6_1008a.jpg,4f57dc_1008d.jpg,9205eb_1008b.jpg,cb1c14_1008.JPG,ad8884_1008c.jpg

the result would look like this:

lg_75c552_599d.jpg,lg_a3a180_599a.jpg,lg_5c2ec4_599e.jpg,lg_dbb052_599b.jpg,lg_419420_599f.jpg,lg_8eb065_599c.jpg

note: images names are different because I just pasted what I already completed manually.

Answer 1

This should be on SuperUser.

What I would do is do Find..Replace on the comma and replace it with ",lg_" (without quotes). Then you need to prepend the strings with "lg_". You can do this by using another column and doing ="lg_"&A1 where you replace A1 with the proper cell reference. Drag this formula down and then copy and paste value that over the initial data.

Edit: Just because you seem new to Excel: To do Find..Replace hit Ctrl + H. The dialog box that will pop up is self explanatory.

Answer 2

This can also be done using the SUBSTITUTE command in a cell formula. e.g. Set C30 = "lg_" Set D30 = "b3f9e6_1008a.jpg,4f57dc_1008d.jpg" then in E30 type the following: =C30&SUBSTITUTE(D30,",",","&C30)

This does the same as ApplePie's answer, but with the added benefits that you do not need to do a manual replace, and "lg_" is not hard-coded so it can change depending upon the contents of a cell. Therefore you can copy the formula down a column and use different prefixes for each row. e.g.

  IF C30="244" and D30="91,99" then E30 = "24491,24499"
  IF C31="376" and D31="5,6,7" then E31 = "3765,3766,3767"