Find and Replace text within string

Hi,

I'm new to this forum and only starting out with VBA in excel and I am wondering if anyone here can help me.

I am looking for a bit of code to search an external link e.g. U:/Workbooks/[Example123456.xls]Sheet1'!$E$10
and replace the 123456 only with a cell ref. for example:

if
A2 = 654321

then
link=U:/Workbooks/[Example654321.xls]Sheet1'!$E$10

if
A2 = 555555

then
link=U:/Workbooks/[Example555555.xls]Sheet1'!$E$10

the numbers are always 6 chars long and I need it to do this across serveral sheets

A1=Sheet1 A1:O40
A2=Sheet2 A1:O40

and so on.
This is what I have currently

Sub ReplaceFC()
    Dim Rng As Range
    Dim c As Range
    Dim Length As Integer
    Dim LeftSide As Integer
    Dim RightSide As Integer
    With Sheets(3).Range("A:O")
        Set Rng = Range("A1:O" & Range("O65536").End(xlUp).Row)
        For Each c In Rng
            LeftSide = InStr(c, "[")
            RightSide = InStr(LeftSide + 13, c, "]")
            Length = RightSide - LeftSide
            If Length = 13 Then
                c.Characters(Start:=LeftSide, Length:=Length).Replace = Sheets(2).Range("h2")
            End If
        Next c
    End With
End Sub

Many thanks in advance

Graham