Thread: Help with tricky formula needed

I have data in column A that I need to get over to column B.

For example column A may contain the number 70. I need that number to translate into a web url in colum B in format http://abc.com/thumbs/000/000/070/small.jpg

For example if I had the number 17825, the corresponding entry in colum B would need to look like http://abc.com/thumbs/000/017/825/small.jpg

150555 would be http://abc.com/thumbs/000/150/555/small.jpg and so on....

Largest numbers are 6 figures. Can anyone help me with a formula to do this?

Hi

If your number is in A2, try
=HYPERLINK("http://abc.com/thumbs/000/" & LEFT(TEXT(A2,"000000"),3) & "/" & RIGHT(TEXT(A2,"000000"),3) & "/small.jpg")

rylo

Hi cashbagg,

Assuming your data starts at cell A2 (change as required), put the following formula in cell B2 and copy down as required:

=HYPERLINK(A2,"http://abc.com/thumbs/000/"&IF(LEN(A2)=6,LEFT(A2,3),IF(LEN(A2)=5,"0"&LEFT(A2,2),IF(LEN(A2)=4,"00"&LEFT(A2,1),"000")))&"/"&IF(OR(LEN(A2)=6,LEN(A2)=5,LEN(A2)=4,LEN(A2)=3),MID(A2,LEN(A2)-2,3),IF(LEN(A2)=2,"0"&A2,IF(LEN(A2)=1,"000"&A2,"000")))&"/small.jpg")

HTH

Robert

Cashbagg,

Please read our Forum Rules about thread titles before starting your next thread.

Try

=HYPERLINK("http://abc.com/thumbs/000/"&TEXT(A2,"000\/000")&"/small.jpg")

Robert you are a scholar and a gentleman!

Worked like a charm,

Thanks.

Robert you are a scholar and a gentleman!

- despite the rumours

Thanks for the feedback, but I'd actually use either rylo's or daddylonglegs's formula as they're far more succinct and easier to understand / follow.