Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,912
- Office Version
- 365
- Platform
- Windows
Hi All
I'm a bit rusty on the ol' regexp and I think I have been trying too hard for too long so I've lost the plot. Any help appreciated...
I have a string in cell E11:
Find each sub-string that is like "< ?* >" (excluding spaces!)
Look for the contents (e.g. ISIN) in row 10
Replace the entire sub-string with the cell address that refers to row 11 and the column index where the sub-string was found in row 10
So to give an example... Column labels in row10. ISIN is in column A, TYPE is in G. The result should be:
Edit: < ISIN > and such substrings don't actually contain any spaces. I put these in to prevent it from being parsed.
I'm a bit rusty on the ol' regexp and I think I have been trying too hard for too long so I've lost the plot. Any help appreciated...
I have a string in cell E11:
Code:
[PLAIN]||=IF($B$8,< ISIN > & " " & IF(< TYPE >="Bond",IF(ISBBERR(BDP(< ISIN > & " Corp",SECURITY_NAME)), "Mtge","Corp"),IF(< ISIN >="Equity","Equity")),FALSE)||[/PLAIN]
Find each sub-string that is like "< ?* >" (excluding spaces!)
Look for the contents (e.g. ISIN) in row 10
Replace the entire sub-string with the cell address that refers to row 11 and the column index where the sub-string was found in row 10
So to give an example... Column labels in row10. ISIN is in column A, TYPE is in G. The result should be:
Code:
||=IF($B$8,[B][COLOR="Red"]A11[/COLOR][/B] & " " & IF([B][COLOR="Red"]G11[/COLOR][/B]="Bond",IF(ISBBERR(BDP([B][COLOR="Red"]A11[/COLOR][/B] & " Corp","SECURITY_NAME")), "Mtge","Corp"),IF([B][COLOR="Red"]A11[/COLOR][/B]="Equity","Equity")),FALSE)||
Edit: < ISIN > and such substrings don't actually contain any spaces. I put these in to prevent it from being parsed.
Last edited: