Question posted 2013 · +5 upvotes
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!
Accepted answer +11 upvotes
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)
2 code variants in this answer
- Variant 1 — 1 lines, starts with
=MID(A1, FIND(":", A1, 1)+1, LEN(A1) - FIND(":",A1,1)) & ":… - Variant 2 — 1 lines, starts with
=HYPERLINK("http://www.site.com/page?id="&B1)
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
.