Geek asks and answers

How to check spaces before or after a line in excel

I have a column containing values ​​scattered across multiple rows. Some of the values ​​have a space either in front or at the end of the value. Is there an easy way to find out such occurrences in an excel sheet.

+8
source share
5 answers

Assuming you have data in column A, you can enter the following formula in any column starting at row 1 and drag down to define spaces

=IF(OR(LEFT(A1,1)=" ",RIGHT(A1,1)=" "),TRUE,FALSE)

TRUE will identify data with a leading or trailing space

+9
source

Yes there is. Thus, the original length

=len(A1)

correctly? If you crop it and it returns a different length, then there was a place at the beginning or at the end of the value, which would be -

=if(len(A1)-len(trim(A1))>0,"yes","no")
+4

? , ( , , , ):

=IF(LEN(A1)-LEN(TRIM(A1))>0,"SPACE!","")

B1. , / . , @YograjGupta, true, .

enter image description here

+1

, - ,

=IF(CODE(LEFT(<reference cell>,1))=160,"Leading Space","No Leading space")
0

If none of the other answers in this post work, but the Sachin Pandit "CODE (160)" formula does, then you actually did not have any "spaces" as such [CODE (32)], but there are several inextricable spaces. To deal with text that has a double (or more) CODE (32) space between words, you can use this formula:

=IF(OR(LEFT(SUBSTITUTE(A1,"  ",""),1)=" ",RIGHT(SUBSTITUTE(A1,"  ",""),1)=" "),TRUE,FALSE)
0
source

More articles:


All Articles