Text data cleaning

[Mashtonsmith1]

Hello,

I need to run two tests on text fields ideally using one formuale.

The first test need to look for 2 consecutive spaces. (e.g. WORD__WORD) as opposed to just 1 space between words.

The second test then needs to look for 1 or more spaces AFTER the last entry. (e.g WORD_WORD_) or (WORD_WORD__)

Entries are of various lengths.

Can I do this with an IF formula? Ideally the resultant cell shows either "Double space" or "Spaces at end" or both.

I dont want to TRIM or cleanse this just yet - just identify where these tests are true.

Thank you

 

[Joe4]

Here is one way (for an entry in cell A1):

=IF(AND(ISNUMBER(FIND("  ",A1)),RIGHT(A1,1)=" "),"Both",IF(ISNUMBER(FIND("  ",A1)),"Double Space",IF(RIGHT(A1,1)=" ","Spaces at end","None")))

If you don't want the word "None" if neither condition is met, just change "None" to "".

 

[Mashtonsmith1]

This works perfectly. Thanks very much.

 

[Joe4]

You are welcome.
Glad I was able to help!