STRIM is a complete "space" removal solution to conveniently manage leading, trailing, or all spaces within a string
STRIM stands for "space trim". STRIM allows for conveniently specifying what type of spaces to remove form a string. The choices are: leading spaces, trailing spaces, all spaces, and TRIM-style space removal (where all spaces except one space in between words are removed).
Please feel free to share any thoughts or suggestions.
This work is based on the discussion at the following thread:
STRIM stands for "space trim". STRIM allows for conveniently specifying what type of spaces to remove form a string. The choices are: leading spaces, trailing spaces, all spaces, and TRIM-style space removal (where all spaces except one space in between words are removed).
Excel Formula:
=LAMBDA(text,space_removal_type,
SWITCH(space_removal_type,
"leading",
LET(a,LEN(text),
(RIGHT(text,a-(MATCH(TRUE,INDEX(CODE(MID(text,ROW(INDIRECT("1:"&a)),1))<>32,),0)-1)))),
"trailing",
LET(a,LEN(text),
(LEFT(text,a-(a-AGGREGATE(14,6,ROW(INDIRECT("1:"&a))/(CODE(MID(text,ROW(INDIRECT("1:"&a)),1))<>32),1))))),
"all",
SUBSTITUTE(text," ",""),
"trim",
TRIM(text)
)
)
Blank power workbook1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | ||||||||||
2 | Spaces | remove leading | remove trailing | remove all | trim | |||||
3 | none | go home now | go home now | go home now | gohomenow | go home now | ||||
4 | leading | go home now | go home now | go home now | gohomenow | go home now | ||||
5 | trailing | go home now | go home now | go home now | gohomenow | go home now | ||||
6 | middle | go home now | go home now | go home now | gohomenow | go home now | ||||
7 | middle | go home now | go home now | go home now | gohomenow | go home now | ||||
8 | leading/middle | go home now | go home now | go home now | gohomenow | go home now | ||||
9 | trailing/middle | go home now | go home now | go home now | gohomenow | go home now | ||||
10 | leading/trailing | go home now | go home now | go home now | gohomenow | go home now | ||||
11 | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:D10 | D3 | =STRIM($C3,"leading") |
E3:E10 | E3 | =STRIM($C3,"trailing") |
F3:F10 | F3 | =STRIM($C3,"all") |
G3:G10 | G3 | =STRIM($C3,"trim") |
Please feel free to share any thoughts or suggestions.
This work is based on the discussion at the following thread:
How to count the total number of leading and trailing spaces in cells?
Hello, I'm trying to come up with a way to count leading and trailing spaces in cells. I have seen posts discussing ways of trimming such spaces, but I didn't see anything about counting them. (The reason I need this is that it will help me identify the source software from which some of my...
www.mrexcel.com
Upvote
0