Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
SUM formula - using variable values | Excel Worksheet Functions | |||
use of variable in formula / execute4excel | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |