Can't remove excessive spaces within a string

I have the following situation.
After importing data from a csv file certain cells contain strings that look like the one below.

Please Login or Register  to view this content.

As you can see at 2 places in the string there are excessive spaces. Infact, typically I get a carriage return followed by excessive spaces after a superscipt or subscript letter. I wrote a script that can remove the carriage returns but I don't know how to write a script to remove the spaces. Note: The lenght and the number of the spaces in a string varies.

If you know how to remove these spaces with a VBA script please share it with me.

The underlying problem is with the CSV files. I don't have control over the creation of the CSV files. The only way I can solve the problem from my end is by removing the carriage returns and excessive spaces after obtaining the CSV files.

Could you send excel file with sample?

You can substitute 2 spaces with 1 space until all spaces a single

Hi,

Does the following non VBA (formula) solution help?

=SUBSTITUTE(SUBSTITUTE(A1," ","|"),"|","")

And what about TRIM function? (builtin or in VBA?)

Richard idea is best...
Just put space in second substitute:

=SUBSTITUTE(SUBSTITUTE(A1," ","|"),"|"," ")

Try function =trim(a1) and if the result is ok then it will be easy to transfer this in vba.

TRIM formula will remove spaces from left and right ends as well as multiple spaces within text.
The help says.

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

VBA TRIM function only tops and tails a string. But you can use the worksheet function in VBA.

Please Login or Register  to view this content.

You will get different results between that formula and TRIM depending on the number of consecutive spaces in the original string.

Test the following 3 simple texts, where the last part denotes the number of spaces following the word Test.

Please Login or Register  to view this content.

I took Andy's script line and incorpoareted it adjusted into my macro. It works.
Problem solved.

Thanks everybody for all the great suggestions.

Hi,

Sorry for the typo. As Zbor has pointed out there should have been an additional space in the second element of the second substitute. Just to repeat

=SUBSTITUTE(SUBSTITUTE(A1," ","|"),"|","")

will return

Epidermal growth factor chronically upregulates Ca2+-dependent Cl- conductance and TMEM16A expression in intestinal epithelial cells.

If you need to incorporate it in macro then just write the macro so that it builds the SUBSTITUTE formula for you.