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.

solved: final result

question from:https://stackoverflow.com/questions/65866987/create-hyperlink-using-cell-value-and-some-additional-text-in-excel

Answer 1

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,"&","_"),"-","_")