SUBSTITUTE formula -- variable spacing between parameters??

[The Moose]

I have cells with text like this:

This touching, two-sided cherry wood plaque has "A Child's Prayer" on
one side and "A Parent's Prayer" on the other. 8 1/2" x 10 7/8" high.
1932 $9.95

I want to remove the SKU and the pricing at the end of the paragraph.

I entered this formula in another cell:

=SUBSTITUTE($W2;" "&$T2&" "&"$"&$Z2;"";1)

To get this result:

This touching, two-sided cherry wood plaque has "A Child's Prayer" on
one side and "A Parent's Prayer" on the other. 8 1/2" x 10 7/8" high.

Works beautifully. EXCEPT, in the two areas in the formula above where
I have concantenated spaces, the number of spaces varies. Sometimes
it's 2 on the left and 2 on the right. Somestimes it's 1 on the left
and 2 on the right. Sometimes, it's 2 on the left and 1 on the right.
Looks like they have three different people doing data entry :GRIN:

Anyone got any idea how to substitute variable number of spaces??

I tried:

=SUBSTITUTE($W2;"*"&$T2&"*"&"$"&$Z2;"";1)

No joy. I also tried question marks -- no joy.

Thanks.

Barb

 

[The Moose]

Elkar/JMB,

WOW -- I got an echo!!

Thanks, both of you. Works beautifully. EXCEPT, looks like they hired
a daughter of the owner -- one more way of entering the data -- there's
a handful of entries out of thousands that only have the price on the
end. I can search for those after I convert to the CSV upload. Not
really worth trying to figure anymore ways around inconsistent data
entry.

Thanks, both of you. I appreciate it.

Barb

 

[Scott]

Looks like you guys have it solved... but couldn't it be combined in
one statement, ie:

=IF(SUBSTITUTE(TRIM($W2);" "&$T2&"
"&"$"&$Z2;"";1)=TRIM($W2),SUBSTITUTE(TRIM($W2);" "&$T2&"
"&"$"&$Z2;"";1),SUBSTITUTE(TRIM($W2);" "&"$"&$Z2&" "&$T2;"";1))

Looks messy, but it's the same thing repeated 3 times, except for the
FALSE part of the IF, the T2 and Z2 are switched.

Scott

 

[Scott]

=IF(SUBSTITUTE(TRIM($W2);" "&$T2&"
"&"$"&$Z2;"";1)=TRIM($W2);SUBSTITUTE(TRIM($W2);" "&$T2&"
"&"$"&$Z2;"";1);SUBSTITUTE(TRIM($W2);" "&"$"&$Z2&" "&$T2;"";1))

Oops... commas should be semi-colons.

Scott