Try using the TRIM function.

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

HTH,
Elkar


"The Moose" wrote:

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