Replacing double spaces with single spaces

[EdWoods]

Hi,

This problem seems so easy to fix but I'm doing something wrong and I just can't figure it out.

I have a macro that generates text (actually Amounts as Numbers to Amounts as Text).

However in some cases it inserts double spaces, which I'd like to remove.

I can use the "substitute" formula in the worksheet, however I'd rather fix the problem from within the macro. Is there a simple way to do this?

Thanks, and sorry for being so dumb. Must be the alcohol finally taking effect after all these years!


Cheers, Paul.

[:) Sixthsense :)]

Please provide the code so that we can have a look to fix it

[EdWoods]

Okay, here it is.

When I put in (say) 3000, it has double spaces before the word "dollar". If I put in (say) 3210, it's fine.

Please Login or Register  to view this content.

[jindon]

Replace function is similar to Substitute function.

However WorksheetFunction.Trim method should do it
e.g

Please Login or Register  to view this content.

[EdWoods]

They're not leading or trailing spaces, there in the middle of the string...

[:) Sixthsense :)]

You are talking about which function? with which value its getting more spaces?

[Alf]

Try changing line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf

[EdWoods]

As I mentioned before,

When I put in (say) 3000, it has double spaces before the word "dollar". If I put in (say) 3210, it's fine.

[EdWoods]

Tried doing that, Alf. It mucks it up, having no spaces before the word "Dollar" when I put in (say) 3210.

[:) Sixthsense :)]

I believe you got the solution in Post #7

[EdWoods]

I believe you got the solution in Post #7

Nope, unfortunately not. It then puts NO space if I type in (say) 3210.

[EdWoods]

Sorry, but I'm probably not explaining myself correctly.

I don't want to mess with the main body of the code (tried that) as apart from the double spaces, it works well.

All I want to do is insert some simple(?) code at the end of the SpellNumber function, after the line

Please Login or Register  to view this content.

to reformat SpellNumber text to remove double spaces IF they exist. Is it InString/Substitute or something along those lines?

[:) Sixthsense :)]

Try Replacing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

[EdWoods]

Try Replacing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

Awesome! Fixed. Thanks.