In column A I have rows of plain URLs, and in column B I have rows of text.
I wish to combine these so the text of B is shown but is a hyperlink containing the URL of A.
I tried a formula in the C column “=HYPERLINK(A2,B2)” and it provided exactly what I wanted except that C is dependant on A and B still existing but I wish to delete them and be left with C which I’ll then use elsewhere in another table. A and B only temporarily exist for the purpose of creating C.
How can I go about this?
Use the formula in C, then select column C and copy it. With C still selected, use right-click and paste values. You should then have your links and will be able to get rid of A and B.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for the reply, though only the text and not the hyperlink got pasted. This is what I've always done whenever wanting to convert from a formula to a value but it doesn't work here as I've mucked about in paste special but none of the options seem to provide a value with both the text and hyperlink in tact. I'm thinking of finding a way to do it in vba but it's slow going as I'm neither a programmer nor experienced in vba so there's a learning curve.
There are instructions at the top of the page explaining how to attach your sample workbook.
Here's a sample attached. I wish for the url to attach to the numbers in the cM column producing the combined column which is the only one I'll keep.
My actual page goes til L17583 but I just copied a few rows of the relevant question.
Would be good to hear how to go about it cheers:p
I think you might need VBA for this, however ...
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Also:
Administrative Note:
Welcome to the forum.
Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.
With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).
Thank you for helping us to help you.
I don't think this can readily be done with a formula. best non-VBA approach might be to edit all the column A cells, one at a time, adding the bits in red:
=HYPERLINK("https://www.ancestry.com.au/discoveryui-matches/compare/FBC90D17-28A5-4E31-9D14-85B0245CF797/with/00BAB0C5-2F1B-4E9C-81EB-562C70621FC2",33)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
A method just hit me after reading your example, the following five lines represent the 5 columns, I copied the below A/C/E to rows 1:17583 and the original link/text columns sat at B/D. Then opened textedit and went "Format>Make Plain Text" and pasted all five columns and 17583 rows, then opened "Find and Replace" where I put a tabbed space into find and nothing in replace and processed all. Then copied the result into one column in excel with the desired result. Still in formula form but luckily all linked and displayed in one column independant from any other columns. Cheers
=HYPERLINK("
link
",
display text number
)
Glad to have helped... albeit accidentally.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks