Text Extraction between hypens

[ysherriff]

Hi,

Without using text to column feature, how can I extract the text "Alabama" from the below string.

I started with the below formula, but it is not working. Any help would be much appreciated.

=SUBSTITUTE(MID(SUBSTITUTE("-" & J11&REPT("-",6),",",REPT("-",255)),2*255,255),"-","")


2016-03 - Alabama- DirectConnect Summary Report.xlsm

 

[ysherriff]

This is a tricky one. So I need one formula that will extract from the end of the date and the last hypen. in the below instance, it would be Alabama and KY-WV, that needs to be extracted

2016-03 - Alabama- DirectConnect Summary Report.xlsm
2016-03 - KY-WV- DirectConnect Summary Report.xlsm

 

[Chihiro]

This should work, as long as the word to extract occurs after 2nd occurrence of "-" and before last occurrence of "-".

=RIGHT(SUBSTITUTE(A2,IF(ISERROR(FIND("-",A2) ),A2,RIGHT(A2,LEN(A2)-FIND("~", SUBSTITUTE(A2,"-","~",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1)),""),LEN(SUBSTITUTE(A2,IF(ISERROR(FIND("-",A2) ),A2,RIGHT(A2,LEN(A2)-FIND("~", SUBSTITUTE(A2,"-","~",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1)),""))-FIND(CHAR(1),SUBSTITUTE(SUBSTITUTE(A2,IF(ISERROR(FIND("-",A2) ),A2,RIGHT(A2,LEN(A2)-FIND("~", SUBSTITUTE(A2,"-","~",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1)),""),"-",CHAR(1),2)))

There's got to be a better way I'd think...

See attached.

Edit: Forgot one last step. Nest everything inside TRIM().

 

[bosco_yip]

Another option :

=MID(A1,FIND("-",A1,FIND("-",A1)+1)+2,LOOKUP(99,FIND("-",A1,ROW($1:$99)))-FIND("-",A1,FIND("-",A1)+1)-2)

Regards
Bosco

 

[Chihiro]

I've got to remember Lookup. Always pleasure to see your formula, Bosco.

 

[bosco_yip]

I've got to remember Lookup. Always pleasure to see your formula, Bosco.

You're welcome.

Regards
Bosco

 

[ysherriff]

this worked really well. Thank you Bosco. I don't know how you do it with these complicated formulas but it worked

 

[Khalid NGO]

Hi ysherriff,

You have the solutions, just confirming, your strings highlighted in blue:

2016-03 - Alabama- DirectConnect Summary Report.xlsm
2016-03 - KY-WV- DirectConnect Summary Report.xlsm

are fixed?

If yes, you can use simple substitute.

Regards,

 

[ysherriff]

can you please explain Khalid. Thanks for your help. Still learning the various techniques out there.

2016-04 - Alabama- Team Member Log - 1.xlsm

 

[Chihiro]

I believe he means if there is fixed string in front and after you can Substitute out those with blanks. Something like...
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"2016-03 - ",""),"- DirectConnect Summary Report.xlsm",""))

 

[Khalid NGO]

can you please explain Khalid.

Yes, exactly what Chihiro explained above.

Still learning the various techniques out there.

2016-04 - Alabama- Team Member Log - 1.xlsm

May this be another option, but it is dependant on your string pattern, i.e. Your state Names always starts from 11th position, and there is always "-" and a space after state names:

=MID(LEFT(A1,FIND("- ",A1,11)-1),FIND(" - ",LEFT(A1,FIND("- ",A1,11)))+3,99)

This will fail if the pattern is changed.

Regards,