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 |
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 |