Reading a string in one cell and using part of it to fill another

[gambit]

Hi all

I need to read a part of a string in one cell and then capture part it and use the bit of info to fill a part of another cell.

Eg

Cell1: (text)GLDQ

then I need to take only the GLD part and place it in another cell in between some more text, like so;

Cell2: =gllinks|Mkt!'(here),ClosePrice'

and I need to do that over about 70 rows each with a different bit of info.

Can anyone help me on how to beging writing a macro on how to do this.

[jasoncw]

I think your current request is too generic.

For example, are all the cells to find 4 characters in length (e.g. GLDQ), or will they vary?

Is the text you need always 3 characters in length? If not, do you have a range in your spreadsheet listing the lookup strings?

Is there a certain place to insert it into the new string, and it is consistent for all cells? Can you give more examples?

[gambit]

I think your current request is too generic.

For example, are all the cells to find 4 characters in length (e.g. GLDQ), or will they vary?

Is the text you need always 3 characters in length? If not, do you have a range in your spreadsheet listing the lookup strings?

Is there a certain place to insert it into the new string, and it is consistent for all cells? Can you give more examples?

The text I need will always be 3characters in length but the range of the rows to scan will change.

The insertion point for the text (always 3chracters) will always be the same and will be consistent for all the cells.

Eg
col A
Row1 SCNQ
Row2 CSLQ
Row2 ADWQ

The target cell will always be in the same colom and the 3chracters will always need to be in the same place EG

col G
Row1 =gllinks|Mkt!'SCN,ClosePrice'
Row2 =gllinks|Mkt!'CSL,ClosePrice'
Row3 =gllinks|Mkt!'ADW,ClosePrice'

In the examples there are 3 rows and this is the only thing that may change, today there may be three but tomorrow there may 10 or 100.

I hope this helps

[gambit]

can anyone suggest anything that could help me archive this,

Thanks

[Carim]

Hi,

="=gllinks|Mkt!'"&MID(A1,LEN(A1)-3,3)&",ClosePrice'"

[gambit]

excellent thanks, but how would I get that to repeat along all the rows that contain information.