Excel 2019 How to find a " " (space) in a string and only return those strings that have &-CodePudding

Excel 2019 Windows 10

I have 26352 cells, each on a seperate row, that contains a text string of various lengths.

Some of the text strings have been entered incorrectly by a previous user and a " " blank space was introduced, some times 2 or more.

I need a formula or VBA function that will look at each text string and IF it contains a " " blank space or spaces then it returns that string. If there are no blank spaces it returns nothing (ignores it).

I have tried the FIND function but to my laymans eyes it requires a LEFT, MID or RIGHT element but I need the WHOLE string to be searched.

For illustration purposes

A perfect string looks like: Loremipsumdolorsit[amet],(consectetur)_adipiscing_elit

A string with the error looks like Loremipsumdolor sit[amet],(consectetur) adipiscing_elit

So the formula or VBA would only reurn or display the string with the error.

Thanks.

CodePudding user response：

Why bother doing a find.

Just use substitute() and replace any spaces if they exist. You may need iferror() as well.

CodePudding user response：

Answer provided by a work collegue

Assume Cell A1 is the first row & cell with text string to be tested and then copy the formula down the column.

=IF(ISNUMBER(SEARCH(" ",A1)),A1,"")

CodePudding user response：

I would use

=TEXTJOIN(", ",1,UNIQUE(IFERROR(SEARCH(" ",$A1,ROW($A$1:$A$1000)),""),0,0))

This will either return a blank value, or will return the character that every space appears on that line, up to a 1000 character string.