Excel: Adding cell contents after file path

[joey1211]

I have a folder containing book covers named using their ISBN code. In Excel I have a column containing these ISBN codes. I need to add these ISBN codes into another column which has the file path of the folder containing the covers.

ISBN Covers
AAA C:\Covers\AAA.jpg
BBB C:\Covers\BBB.jpg
CCC C:\Covers\CCC.jpg

Any ideas?

 

[Greboth]

Using your example path above, just type

="C:\Covers\"&A1&".jpg"

where A1 is the cell containing the ISBN number. Drag the formula down the list and it will substitute each relevant ISBN number in from cell A2, A3, A4 etc.

That will simply give you the file path as a text string, if you want to be able to click it to open the .jpg of the cover then go for

=Hyperlink("C:\Covers\"&A1&".jpg")

Edit:
If you're folder paths are different and you have a list of these too then you could go for

=A1&B1&".jpg"

Where A1 is your folder path and B1 is your ISBN number. Again like above, you could hyperlink it if you need to.

 

[joey1211]

Many thanks Greboth, worked like a charm.

Is it also possible to add some characters into each colums so that you can use them for formatting purposes in InDesign? So, in front of all the authors I would like $1$, in front of all the book titles $2$ etc.

 

[joey1211]

Method 1 in the following link has worked for the authors and titles but using it for the price delete's the pound sign.

https://www.extendoffice.com/documents/excel/670-excel-add-text-to-beginning-end-of-cell.html

I can overcome the £ disappearing by replacing the $2$ attributed to it with a $ sign in InDesign.

Is it possible to save the Excel spreadsheet to a text file without the columns that don't have $ signs in them? I cant delete the original column as it disappears from the formatted one. Is it possible to convert the imported text into actual text if that makes sense. Allowing me to delete the column without $ signs?

 

[Greboth]

Merging / Concatenating cells this way will remove any formatting as Excel will simply take the true value of the cell rather than what it has been formatted too. However you can add any text, values or symbols in to the formula as anything you put between "" will be treated as text.

So for the authors you could have ="$1$"&A1 and titles would be ="$2$"&B1 (obviously substitute the correct cell references in place of A1 and B1)

The same could be done for the price by adding a "£" before the cell with the price in it. So for example, if your price is in C1 you can put something like ="$3$£"&C1 or =A1&B1&"£"&C1.

On a side note, if you're working with names then you can also use "" to add space where necessary. So for example, =A1&B1 would give the name JohnSmith without a space, =A1&" "&B1 would give John Smith with the space separating the names.