Excel 2019 How to find a " " (space) in a text string and only return those strings that have " " (space/s)

[Event2020]

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 know I could use substitute() and replace any spaces but that is not what I am trying to achieve, I just want a list of all text strings that have the errant " " space.

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.

 

[shift-del]

Hi

Use the filter. Type a space in the search box and hit ENTER.

 

[rollis13]

Supposing that your data starts in A1 in B1 (or other column as helper) use this formula and pull down:
=IF(LEN(SUBSTITUTE(A1," ",""))<>LEN(A1),"Error","Ok")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you just want a list with no blanks, or should this be row by row?

 

[Event2020]

Answer unexpectedly 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,"")

This will return only those text strings that have a " " anywhere within it.

 

[NdNoviceHlp]

How do U want the offending strings displayed? Seems like U could use the Instr function something like...

Dim Rng as range
For each Rng in Activesheet.UsedRange
If Instr(Rng.Text, " ") then
'display something
MsgBox Rng.Address
End if
Next Rng

HTH. Dave

 

[Event2020]

Hi Fluff

I have update my profile as you suggest but I did also state Excell 2019 and Windows 10 at the top of my original post.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

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

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 &qu...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.