create hyperlink using cell value and some additional text in excel

Question

I have excel sheet containing column with values such as

BANDHANBNK
SRF
SRTRANSFIN
L&TFH
IBULHSGFIN
FEDERALBNK
PNB
PEL
VOLTAS

I want to create hyperlink for each of this, url can be created as

https://in.tradingview.com/chart/?symbol=NSE:ACC1!

so I need to concatenate

https://in.tradingview.com/chart/?symbol=NSE: + cell value + 1!

doing this manually is too much work, is there any simpler way to do this?

one more thing is if cell value contains & or - it should be converted to underscore.

it worked. can you tell me how to replace & or - by _ (underscore) while creating this link — user4599
– user4599, Commented Jan 24, 2021 at 5:08
Use SUBSTITUTE() function. See my edited answer. If it solve your problem then consider accepting the answer. — Harun24hr
– Harun24hr, Commented Jan 24, 2021 at 6:37

Harun24hr · Accepted Answer · 2021-01-24 06:41:23Z

7

Use following formula. Here simply concatenating cell value with URL then concatenate 1!. Use Hyperlink() formula to make hyperlink.

=HYPERLINK("https://in.tradingview.com/chart/?symbol=NSE:" & A1 & "1!",A1)

To replace & and - by underscore _ use below SUBSTITUTE() formula.

=SUBSTITUTE(SUBSTITUTE(A1,"&","_"),"-","_")

edited Jan 24, 2021 at 6:41

answered Jan 24, 2021 at 4:41

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4599 Over a year ago

i have created macro to open multiple hyperlinks at same time stackoverflow.com/q/65879870/14713550 . this macro works on hyperlinks that i create such as www.google.com , but somehow it doesnt work on the hyperlinks that i create using the method you mentioned, can you tell me why this is happening

user4599 Over a year ago

for more clearity, i have edited the question that i have mentioned above please look. @Harun24HR

user4599 · Accepted Answer · 2021-01-24 06:53:35Z

1

this worked for me.

=HYPERLINK("https://in.tradingview.com/chart/?symbol=NSE:" & SUBSTITUTE(SUBSTITUTE(E2,"&","_"),"-","_") & "1!",E2)

answered Jan 24, 2021 at 6:53

user4599

1371 gold badge3 silver badges15 bronze badges

Collectives™ on Stack Overflow

create hyperlink using cell value and some additional text in excel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related