Replace Cell Ref in Formula with Value

[mursie]

Can someone please help me do the following:
background: Cell E10 has a six digit number in it, for example 472002

original code: =formula($C$1,$C$2,"Text^E10]")
after code: =formula($C$1,$C$2,"Text^472002]")

I need to change the following: E10 to its calculated value

I need to be able to enter a six digit number and with the use of a macro... this formula has the link replaced with the actual six digit number that is in cell E10.

Is this possible?


wow - finally got this to post but i had to completely butcher the original code. please note that formula and Text were substituted for COGNAME and a long reference string respectively. for some reason the board would not let me post it. hopefully this doesn't hinder the concept of what im trying to achieve.

[excelliot]

Hi

Do you want to convert Cell ref after sign "^" to value or there are many such reference.