Extract text between a character and first space left of that character.

[gcefaloni]

Hi,

The title says it all. I'm trying to extract the text between a character (character is "%") and the first space to the left of that "%" character. I figure it would need to include the left and find functions but I am having a hardtime figuring out how to put it all together.

Any help would be greatly appreciated.

Thanks!

 

[kweaver]

One way: =MID(A1,1+FIND("%",A1),FIND(" ",A1,FIND("%",A1))-FIND("%",A1)-1)

With your string in A1 (and fill down, if more strings). This pulls the text after the % until the first space after the %.
Or was it to be the other way around...the space preceding the %?

 

[Rick Rothstein]

With your text in cell A1 and assuming the percent sign is no more than 300 characters from the beginning of the text (change the 300's if could be more), give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)-1)," ",REPT(" ",300)),300))

 

[gcefaloni]

One way: =MID(A1,1+FIND("%",A1),FIND(" ",A1,FIND("%",A1))-FIND("%",A1)-1)

With your string in A1 (and fill down, if more strings). This pulls the text after the % until the first space after the %.
Or was it to be the other way around...the space preceding the %?

Yes it was the space preceding. But thanks a lot for proposing your solution. It was very appreciated.

 

[gcefaloni]

With your text in cell A1 and assuming the percent sign is no more than 300 characters from the beginning of the text (change the 300's if could be more), give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)-1)," ",REPT(" ",300)),300))

Works like a charm.. Thank you so much!

 

[salim hasan]

Try this formula

=LEFT(REPLACE(A1,1,SEARCH(" *%",A1),""),SEARCH("%",REPLACE(A1,1,SEARCH(" *%",A1),""))-1)