-2

I have a list with names and I want to make an other colum with http://name.jpg

is this possible in excel?

and if my original name was 2 words or more can I have auto underscores in the url instead of spaces?

Thanks

6
  • Try: =CONCATENATE("http://"; A1; ".jpg") – xangr Sep 29 '14 at 17:02
  • 1
    @xangr This looks like a viable solution to the asker's question. Please post your response as an answer, not as a comment. That way it can be accepted as a solution and you can earn reputation for it. – Wes Sayeed Sep 29 '14 at 17:09
  • None of the answers except one actually produces a hyperlink. – Mr. Mascaro Sep 29 '14 at 20:18
  • @jbarker2160 The question doesn't specify that a hyperlink is needed. – Excellll Sep 29 '14 at 21:15
  • 1
    @Excellll, So...the question does not contain blue, underlined text that may be clicked? – Mr. Mascaro Sep 29 '14 at 21:30
1

Try:

=CONCATENATE("http://"; A1; ".jpg")

Also, this will detect spaces and split them with slash:

=IF(LEN(SUBSTITUTE(A1; " "; ""))=LEN(A1); CONCATENATE("http://"; A1; ".jpg"); CONCATENATE("http://"; LEFT(A1;FIND(" ";A1)-1); "/"; MID(A1;FIND(" ";A1; 999); ".jpg"))

This will detect spaces and replace them with underscore:

=IF(LEN(SUBSTITUTE(A1; " "; ""))=LEN(A1); CONCATENATE("http://"; A1; ".jpg"); CONCATENATE("http://"; SUBSTITUTE(A1," ","_"); ".jpg"))

2
  • Hi, that is a very good idea but I 'll give you an example of some names I have for example I have "Anafenza, the Foremost" in one cell and I need to remove the spaces, comas, and replace spaces with _ for the final format – Alex Stavrianos Sep 29 '14 at 17:17
  • @AlexStavrianos, sure. Update the original post. – xangr Sep 29 '14 at 17:21
1

Code:

=HYPERLINK(CONCATENATE("http://", SUBSTITUTE(SUBSTITUTE(A1," ","_"),",",""), ".jpg"))
1
  • While this may answer the question, it would be a better answer if you could provide some explanation for why it does so. – DavidPostill Sep 30 '14 at 4:42
0

You could do something like this:

="http://"&SUBSTITUTE(A1," ","_")&".jpg"

This should meet both requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.