Forum Discussion
Formula for extracting nth field from long delimited text string
I'm currently using the following formula to extract the nth field (n=7) from a text string located in cell A1 and delimited by » Char(187): =MID(A1,FIND("®",SUBSTITUTE(A1,CHAR(187),"®",7))+1,FIN...
Perhaps you could try:
=TRIM(MID(SUBSTITUTE(A1,CHAR(187),REPT(" ",LEN(A1)),8),FIND("®",SUBSTITUTE(A1,CHAR(187),"®",7))+1,LEN(A1)))
But, the first part of the formula will double the length of the string, so if you have very long strings (like 16k characters) it could exceed excel's maximum length (and may be why your other attempt isn't working as it's replacing each space with a string of spaces equal to the length of the string).
=TRIM(MID(SUBSTITUTE(A1,CHAR(187),REPT(" ",LEN(A1)),8),FIND("®",SUBSTITUTE(A1,CHAR(187),"®",7))+1,LEN(A1)))
But, the first part of the formula will double the length of the string, so if you have very long strings (like 16k characters) it could exceed excel's maximum length (and may be why your other attempt isn't working as it's replacing each space with a string of spaces equal to the length of the string).