VBA - spaces

[ericlch16]

Hello i have a string that might have multi spaces between the string but random spaces such as

test er <more than 1 space > kllk <more than 1 space > l
b <more than 1 space > w e <more than 1 space > tttttt klfdgd
klllll <more than 1 space > lll


* this thread is removing the multi spaces to 1 space. hence <the more than 1 space>

i wanted to replace all the spaces that are more than 1 to only 1 space so that the string becomes

test er kllk l
b w e tttttt klfdgd
klllll lll

I was going to going to use REPLACE function in VBA but this would not work as number of spaces varies. Any quick function i can use?

 

[Fluff]

You can use the Excel Trim function like

Application.Trim("a string")

 

[Joe4]

As long as they are normal spaces, the built-in Excel function TRIM will remove duplicate spaces.

 

[ericlch16]

As long as they are normal spaces, the built-in Excel function TRIM will remove duplicate spaces.

The trim will only remove the space infront and at the end of a string. if there are 5 spaces between 2 words in a string, it will not change it to 1. for example string is tt<5 space>XX, the string will still be tt<5 space>XX after TRIM

I want to change the string to

tt<1 space>XX

 

[Joe4]

The trim will only remove the space infront and at the end of a string. if there are 5 spaces between 2 words in a string, it will not change it to 1. for example string is tt<5 space>XX, the string will still be tt<5 space>XX after TRIM

I want to change the string to

tt<1 space>XX

That is not correct. That is the VBA "TRIM" function, not the Excel "TRIM" function (note that I said "built-in Excel function TRIM").

If you want to use an Excel function in VBA, you can do so in the manner that Fluff posted.

 

[Fluff]

You need to use the Excel Trim function & not the VBA version as we said

 

[ericlch16]

That is not correct. That is the VBA "TRIM" function, not the Excel "TRIM" function (note that I said "built-in Excel function TRIM").

If you want to use an Excel function in VBA, you can do so in the manner that Fluff posted.

thanks for the clarification. I will use the function instead. Great experts in this forum

 

[Fluff]

Glad we could help & thanks for the feedback.