How to flag data using left/right contains formula

[slaurel]

I need to make a formula that flags whitespaces in a column of data and then have a second column output that has a marker/some sort of flag next to the data that has trailing whitespaces.

I tried using an IF CONTAINS formula but I keep messing it up. Any idea on how to write this formula?

The column I am pulling from is just titled "Info". and the second column that I want the markers to be can be called "whitespace"

[BS_THE_ANALYST]

Have you got some mock input data? 😊

[slaurel]

@BS_THE_ANALYST Yes. Column header is Info and under it can be

aaa

 56

777

fdr

 ff

ff 

yy

It really doesn't matter. The data can be a variety of numbers, decimals, letters. I just need a column to flag which ones have whitespaces (before and after), so I would use the contains left / contains right formulas (I think)

[BS_THE_ANALYST]

I came up with this. Does this tackle the issue?

[BS_THE_ANALYST]

OH my bad, I see now. If you want to flag the ones which have whitespace both before and after then you can use this following formula:

REGEX_Match([Info], "^\s+.*\s+$").. try it out :-)

[Felipe_Ribeir0]

Hi @slaurel 

If i understood your problem well, it can be solved just by using left and right functions like these:

[BS_THE_ANALYST]

I like the idea you have with using LEFT and RIGHT to count the spaces. However, you might not know HOW MANY spaces there are before or after the text. Thats the annoying thing with LEFT/RIGHT. You have to specify how many characters to count from the left or the right. It doesn't give that 'flexibility' of having varying numbers of spaces from the left, or from the right. This is where we use REGEX. 

To explain the regular expression: ^\s+.*\s+$   

^ means the start (from the left)

$ mean the end (from the right)

\s+ (means AT LEAST one space or more)

.* (means any character, none or more times) 

string this together: ^\s+.*\s+$  

reading it from left to right .. it must start with one or more spaces followed by any characters and then end in any number of spaces 😀.

REGEX_MATCH is basically saying, hey, if this condition is met, I will output '-1'. Thus, you have a flag for words which have leading AND trailing spaces. 

Hope that helps. I only learnt REGEX two weeks ago. Don't be scared of it, I know I was 😂 @slaurel 

[Felipe_Ribeir0]

@BS_THE_ANALYST , for this case i understood that it does not matter if you have 1 or many spaces as long as they are before or/and after the characters.

[BS_THE_ANALYST]

@Felipe_Ribeir0 you're right. Over complicated it. LEFT/RIGHT paired with searching for a single space will satisfy all leading/trailing space cases, regardless of length. 

@slaurel see @Felipe_Ribeir0 's answer. Much more simplistic. 

Nevertheless, Regex > 😂.

[Felipe_Ribeir0]

@BS_THE_ANALYST  regex is insanely useful, so its a good idea to look at your answer too and understand why both approachs works!

As you said, maybe for this simple case the left/right can deal with the problem, but surely soon he will get a case where he will need regex 😅