Dear all,
I would like to incorporate a function to trim excessive white spaces (i.e., >=2 white spaces in between words) into my pre-existing formula, but I'm at a loss for how to do this.
I've attached an excel sheet for your review. In my actual sheet, I have about 50 items, and each item is labeled with a specific stem word to distinguish the categories the items belong to but in the attached sheet, I've only included 2 items and they both belong to the same category of Objectives. Anyway, in these cells, my students can either enter a value of 0, 1, or text that says "Skipped with NA"), and the total possible number of points for these 2 items is 2. The participant gets credit for each item if they got a 1, or a "Skipped with NA".
The formula I've created states this: sum up all the values in columns where the headers contain the string of "Objectives_" AND add those cells where they have "Skipped with NA" entered in them. However, I want my current formula to be flexible enough so that if someone accidentally entered this text with extra white spaces in between the words, the participants would still get full credit for the item. So for example, for row #5, there are two white spaces between the word "Skipped" and the word "with", and I wanted the total score in column C to be 2, instead of 1.
I tried to insert a trim function in various locations in my formula but that did not work.
Can someone please kindly help? Thank you so much for your time, and please reach out if you need clarification.
Anita
I would like to incorporate a function to trim excessive white spaces (i.e., >=2 white spaces in between words) into my pre-existing formula, but I'm at a loss for how to do this.
I've attached an excel sheet for your review. In my actual sheet, I have about 50 items, and each item is labeled with a specific stem word to distinguish the categories the items belong to but in the attached sheet, I've only included 2 items and they both belong to the same category of Objectives. Anyway, in these cells, my students can either enter a value of 0, 1, or text that says "Skipped with NA"), and the total possible number of points for these 2 items is 2. The participant gets credit for each item if they got a 1, or a "Skipped with NA".
The formula I've created states this: sum up all the values in columns where the headers contain the string of "Objectives_" AND add those cells where they have "Skipped with NA" entered in them. However, I want my current formula to be flexible enough so that if someone accidentally entered this text with extra white spaces in between the words, the participants would still get full credit for the item. So for example, for row #5, there are two white spaces between the word "Skipped" and the word "with", and I wanted the total score in column C to be 2, instead of 1.
I tried to insert a trim function in various locations in my formula but that did not work.
Can someone please kindly help? Thank you so much for your time, and please reach out if you need clarification.
Anita
| How to remove excessive white spaces.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | 2_Objectives_Item name | 3_Objectives_Item Name | Total Score | Desired Outcome | ||
| 2 | 1 | 1 | 2 | This looks good | ||
| 3 | 1 | Skipped with NA | 2 | This looks good | ||
| 4 | 1 | 0 | 1 | This looks good | ||
| 5 | 1 | Skipped with NA | 1 | Desired Outcome is also 2 | ||
| 6 | Skipped with NA | 1 | 1 | Desired Outcome is also 2 | ||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C6 | C2 | =SUMIF($A$1:$B$1,"*Objectives_*",A2:B2)+COUNTIFS($A$1:$B$1,"*Objectives_*",A2:B2,"*Skipped with NA*") |
