SUBSTITUTE formula -- variable spacing between parameters??
 

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

SUBSTITUTE formula -- variable spacing between parameters??
 

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

SUBSTITUTE formula -- variable spacing between parameters??
 

Try :

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


"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

SUBSTITUTE formula -- variable spacing between parameters??
 

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


JMB wrote:
Try :

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


"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

SUBSTITUTE formula -- variable spacing between parameters??
 

After you enter the substitute function (lets say it is in cell AA2) one
quick check you could do is in AB2, enter

=W2=AA2

which would identify anything that was not changed by the Substitute
function (by returning a TRUE). Filter column AB2 for TRUE values - which
should give you the entries that need special attention. If there are many
left to be changed, you could sort the data by column AB2 to group the
entries that still need changed then modify your substitute function for just
those entries.

If you need to maintain the original order of the list, insert a helper
column (before you begin all this) and number 1 - whatever. When you're all
done sort the list by this helper column to put everything back in its
original order and delete the helper column.



"The Moose" wrote:

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


JMB wrote:
Try :

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


"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

SUBSTITUTE formula -- variable spacing between parameters??
 

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

JMB wrote:
After you enter the substitute function (lets say it is in cell AA2) one
quick check you could do is in AB2, enter

=W2=AA2

which would identify anything that was not changed by the Substitute
function (by returning a TRUE). Filter column AB2 for TRUE values - which
should give you the entries that need special attention. If there are many
left to be changed, you could sort the data by column AB2 to group the
entries that still need changed then modify your substitute function for just
those entries.

If you need to maintain the original order of the list, insert a helper
column (before you begin all this) and number 1 - whatever. When you're all
done sort the list by this helper column to put everything back in its
original order and delete the helper column.



"The Moose" wrote:

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


JMB wrote:
Try :

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


"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

SUBSTITUTE formula -- variable spacing between parameters??
 

=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

Scott wrote:
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

JMB wrote:
After you enter the substitute function (lets say it is in cell AA2) one
quick check you could do is in AB2, enter

=W2=AA2

which would identify anything that was not changed by the Substitute
function (by returning a TRUE). Filter column AB2 for TRUE values - which
should give you the entries that need special attention. If there are many
left to be changed, you could sort the data by column AB2 to group the
entries that still need changed then modify your substitute function for just
those entries.

If you need to maintain the original order of the list, insert a helper
column (before you begin all this) and number 1 - whatever. When you're all
done sort the list by this helper column to put everything back in its
original order and delete the helper column.



"The Moose" wrote:

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


JMB wrote:
Try :

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


"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