Getting data from two cells to make a hyperlink

[Gene]

When I have merged two cells, one cell is from a Concatenation of
another cell, trying to form a hyperlink, I get a error that the
linked file is not there. When, I look to see the hyperlinked cell it
is showing the Concatenation and not the disply. Any help to convert
the Cont. cell to the value and not the formula.

 

[Gene]

When I have merged two cells, one cell is from a Concatenation of
another cell, trying to form a hyperlink, I get a error that the
linked file is not there. When, I look to see the hyperlinked cell it
is showing the Concatenation and not the disply. Any help to convert
the Cont. cell to the value and not the formula.

Example
Cell 1: http://www2.county.allegheny.pa.us/RealEstate/Image.asp?CurrBloLot=
Cell 2 (Concat.):
=CONCATENATE("0",LEFT(J3,3),MID(J3,5,1),"00",RIGHT(J3,3),"000000")
displayed as 0230M00255000000
Cell 3: Merged cell displays as:
http://www2.county.allegheny.pa.us/RealEstate/Image.asp?CurrBloLot=0230M00255000000
but shows the 023...000 as
CONCATENATE("0",LEFT(J3,3),MID(J3,5,1),"00",RIGHT(J3,3),"000000" which
does not work as a hyperlink.

 

[Gene]

Example
Cell 1: http://www2.county.allegheny.pa.us/RealEstate/Image.asp?CurrBloLot=
Cell 2 (Concat.):
=CONCATENATE("0",LEFT(J3,3),MID(J3,5,1),"00",RIGHT(J3,3),"000000")
displayed as 0230M00255000000
Cell 3: Merged cell displays as:http://www2.county.allegheny.pa.us/RealEstate/Image.asp?CurrBloLot=02...
but shows the 023...000 as
CONCATENATE("0",LEFT(J3,3),MID(J3,5,1),"00",RIGHT(J3,3),"000000" which
does not work as a hyperlink.

I think I got it by coping the merged cell and pasteing as values only
but this has resulted in a lot of work. Is there an easier way?