[SOLVED] Need a formula only pick up the name between - and :

[jackson_hollon]

Hello,

I am looking for a formula that will pick up the names in between a - and a :

I have attached a sample file here.

Thank you for the help.

[Crooza]

Try this

Please Login or Register  to view this content.

[jackson_hollon]

Crooza,

It works as needed. Thank you very much.

[FDibbins]

Another version...
=LEFT(MID(A1,FIND(" - ",A1,1)+3,99),FIND(":",MID(A1,FIND(" - ",A1,1)+3,99),1)-1)
or this...
=MID(A1,FIND(" - ",A1,1)+3,FIND(":",A1)-FIND("-",A1)-2)

[Crooza]

@Ford

those two formulas only seem to work when there is a colon. Some of the examples in the spreadsheet were of the form

Exp Rpt - Simbu, Carol

[FDibbins]

Good catch Crooza....
=MID(A1,FIND(" - ",A1,1)+3,IFERROR(FIND(":",A1)-FIND("-",A1)-2,99))

[jackson_hollon]

Ford,

It works perfect as well.

Thanks for the help.

Jackson

[FlameRetired]

And for good measure ... the kitchen sink.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,":",REPT(" ",256)),"-",REPT(" ",256)),256,256))

[FDibbins]

Happy to help and thanks for the feedback

[jackson_hollon]

Dave,

Could you please tell me what is the 256 mean? Why do we need to use 256? Thanks for the help.

jackson

[FlameRetired]

Jackson,

The 256 is there to insert an arbitrarily large number of spaces. This introduces enough "padding" into the string to allow ease of string functions like LEFT, RIGHT and of course MID without chopping off desirable portions of the string as you step through it. Typically this is followed with TRIM wrapped around the formula. This removes all leading / trailing and repeating spaces.

The choice of 256 is one of my habits. It is almost always large enough .... you will sometimes see 99, 20 .... other. The choice depends largely upon what the risk is of chopping off the "keeper" parts. The smaller the number usually the greater that risk. I find the longer the string, the more parsing to be done, the longer the "keeper" parts usually the larger the number I use. 256 is almost always comfortable and safe.

Did this help?

[FDibbins]

Ft used 256, if you look at the end of my suggestion, I used 99 (for a slightly different function, but for the same reason)

[jackson_hollon]

FlameRetired,

It really helped. Thank you for the explanation.

Ford,

Now I got why you used 99. Thanks for the help again.

Jackson