LEN counts "Space" as a character

Haithem83

Hello,

How do I prevent LEN from counting spaces between words as characters?

Thank you.

=LEN([Letters]@row) * 10.75 * Height@row

Jeff Reisman

@Haithem83

Create a helper column. Use the SUBSTITUTE function to replace the blanks in the text with nothing, thereby removing the blanks. Then use LEN against the helper column.

You could also do it without the helper column by placing the SUBSTITUTE within the LEN formula:

=LEN(SUBSTITUTE([Letters]@row, " ", ""))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320161#Comment_320161

=LEN(SUBSTITUTE([Letters]@row, " ", "")) works.

=LEN(SUBSTITUTE([Letters]@row, " ", "")) * 10.75 * Height@row

Jeff Reisman

@Haithem83

Create a helper column. Use the SUBSTITUTE function to replace the blanks in the text with nothing, thereby removing the blanks. Then use LEN against the helper column.

You could also do it without the helper column by placing the SUBSTITUTE within the LEN formula:

=LEN(SUBSTITUTE([Letters]@row, " ", ""))

Haithem83

Works well, although I hope that there's a better way to do it by using exact cell.

Thank you, Jeff.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320161#Comment_320161

=LEN(SUBSTITUTE([Letters]@row, " ", "")) works.

=LEN(SUBSTITUTE([Letters]@row, " ", "")) * 10.75 * Height@row

Haithem83

It works perfectly. Thanks, Jeff, I appreciate it