CODEWITHSUNDEEP
excel
priya
priya

Reputation: 26749

How to check for spaces before or after a string in excel

I have a column containing values spread over several rows. Some of the values have a space either in front or at the end of the value. Is there a simple way to find out such occurences in a excel sheet.

Upvotes: 14

Views: 121682

Answers (5)

gothic chicken
gothic chicken

Reputation: 31

If none of the other answers in this post work, but Sachin Pandit's "CODE(160)" formula does, then you didn't actually have "spaces" as such [CHAR(32)] but rather some non-breaking whitespaces [CHAR(160)]. To deal with text that has double (or more) CHAR(32) spaces between words, you could use this formula:

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

Upvotes: 2

Sachin Pandit
Sachin Pandit

Reputation: 1

Other than below code, rest did not work - This is to only find leading spaces , 

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

Upvotes: 0

John Smith
John Smith

Reputation: 7407

Yes there is. So the original length is 

=len(A1)

right? If you trim it and it returns a different length, then there was a space at either the beginning or end of the value, which would be-

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

Upvotes: 5

RocketDonkey
RocketDonkey

Reputation: 37269

Do you want the check in a separate column? If so, try adding this in a column next to the data (or anywhere you want, as long as you reference the column):

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

This would go in cell B1. Note that this will also catch instances with multiple spaces before/after the string. However as @YograjGupta mentions, this will also return true if there are consecutive spaces inside the string.

enter image description here

Upvotes: 4

user857521
user857521

Reputation:

Assuming you have the data in column A, you could enter the following formula in any column starting from row 1 and drag down to identify spaces

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

TRUE will identify data with a leading or trailing space

Upvotes: 18

Related Questions