• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Text Extraction between hypens

Y

ysherriff

Member
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
 
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
 
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... :p

See attached.

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

Attachments

  • Extract String.xlsx
    8.5 KB · Views: 5
Last edited:
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,
 
can you please explain Khalid. Thanks for your help. Still learning the various techniques out there.

2016-04 - Alabama- Team Member Log - 1.xlsm
 
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",""))
 
ysherriff said:
can you please explain Khalid.
Click to expand...
Yes, exactly what Chihiro explained above.

Still learning the various techniques out there.

2016-04 - Alabama- Team Member Log - 1.xlsm
Click to expand...

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,
 
You must log in or register to reply here.
Back
Top