Extract Text from Middle of Cell Contents

[clortan]

I need help extracting the bold text from the middle of these examples.

Text Text # 1 - Downtown Dallas - 15368 Product Description
Text Text Text # 2 - Northpark Center - 16485 Product Description

I am need the store name, which will always be after the # and space and all the information before the next - and space

Thank you !

 

[RonB1111]

With your text string in A1, try:

=MID(A1,FIND("#",A1)+2,FIND("-",A1,FIND("-",A1)+2)-FIND("#",A1)-2)

 

[FDibbins]

Or this 1, slightly shorter...
=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",50)),50,50))

 

[clortan]

Hi Ron (my brother's name) and Ford (cute dog)

Thank you both! Here is what happened:

With =MID(A1,FIND("#",A1)+2,FIND("-",A1,FIND("-",A1)+2)-FIND("#",A1)-2)

There were varied blank spaces at the beginning and 1 at the end of the output.
___1 Downtown Dallas_
__30 King of Prussia_
_101 Austin_

With =TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",50)),50,50)) - This one almost works. There were no spaces, and if the # could go away at the beginning I would be great!!
#1 Downtown Dallas
#30 King of Prussia
#101 Austin

 

[RonB1111]

Try surrounding the formula with TRIM, like this:

[COLOR=#333333]=TRIM(MID(A1,FIND("#",A1)+2,FIND("-",A1,FIND("-",A1)+2)-FIND("#",A1)-2))

[/COLOR]

If that doesn't solve it, please show us the original text strings.

 

[FDibbins]

Change it to this instead...
=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",50)),50,50))