Menu
Consider the following Excel sheet:
A B C
1 ASX:ANZ ANZ:ASX http://www.site.com/page?id=ANZ:ASX
2 DOW:1234 1234:DOW http://www.site.com/page?id=1234:DOW
3 NASDAQ:EXP EXP:NASDAQ http://www.site.com/page?id=EXP:NASDAQ
I need a formula for the B and the C column. In the B column I need the values of the A column to be split on : and the two resulting parts to be reversed, see the three examples. In the C column, I need the result from B to be added to a (hardcopy) URL (http://www.site.com/page?id=) to form a link.
Who can help me out? Your help is greatly appreciated!
433
In the AutoCorrect dialog, go to the AutoFormat As You Type tab and make sure "Internet and network paths with hyperlinks" is checked. Then you should be able to enter URLs as desired and they will be automatically turned into a hyperlink.
On a worksheet, select the cell where you want to create a link. On the Insert tab, select Hyperlink. You can also right-click the cell and then select Hyperlink... on the shortcut menu, or you can press Ctrl+K.
Click the cell where you want to enter a reference to another cell. Type an equals (=) sign in the cell. Click the cell in the same worksheet you want to make a reference to, and the cell name is automatically entered after the equal sign. Press Enter to create the cell reference.
Alright. I don't normally spoon feed answers but here you go.
In B:
=MID(A1, FIND(":", A1, 1)+1, LEN(A1) - FIND(":",A1,1)) & ":"&MID(A1,1,FIND(":",A1,1)-1)
In C:
=HYPERLINK("http://www.site.com/page?id="&B1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
