Insert spaces in pasted string?

[calbais]

I have a word form where copied text is pasted in a blank line. The first part of this text is a 9 digit number. I have a macro that makes the text bold and I would like to add some code to it that will put a space after every 3rd number so that 123456789 would become 123 456 789.
How can I do this?

Thanks

[fumei]

You do not explain where and how the "copied text" is coming from. In any case to add the spaces use the Left, Mid, and Right functions. These are basic string manipulation functions. you need to know how to use them.
Dim StartString
StartString = "123456789"
StartString = Left(StartString, 3) & " " & Mid(StartString, 4, 3) & " " & Right(StartString, 3)

The StartString variable now is: "123 456 789"

It means:

starting from the LEFT, use a length of 3 characters = 123
add a space
from inside the string (Mid), start at the 4th character, use a length of 3 characters = 456
add a space
fromthe RIGHT, use a length of 3 characters = 789

[fumei]

Obviously if the string contains MORE than the 9 characters, the Right function will not work to get the final 3 characters.

[macropod]

Hi Gerry,

You've been away too long:
StartString = Format(StartString, "000 000 000")

[fumei]

Yikes...I'll say!! That is what you get from not really opening Word (never mind using VBA) for almost a year.

I am suitably ashamed. Talk about taking the long road around. Yikes.

[fumei]

Just to add to Paul\s post...

Say you DO have other text, and you want to put the spaces in your first

[calbais]

Thank you everyone!! I got it working using Fumei's advice. I can see you are a teacher. You gave a clear explanation of the string manipulation functions. I understand how they work now. Thanks very much!!

[fumei]

What? Are you saying that Paul's Format(StartString) does not work??? It certainly does.

[macropod]

Hi Gerry,

I think calbais was impressed with your explanation.

BTW, your solution gives spurious results for a string that is anything other than 9 characters. With less, you'll get overlapping characters and with more, characters will be omitted. With my solution, the left end will either be 0-padded for less than 9 digits or, with more than 9 digits, will contain more than 3 digits before the first space.

[calbais]

Hi Paul
You have been very helpful also. I even learned something from your last post - fumei's name is Gerry...
I haven't tried your way of formatting but I will and I'm sure it will work also.

You both have been very helpful to me and I've learned a lot about VBA. I now have an Excel project on the go and I'm sure I'll be back looking for help from you geniuses!!

Thanks

[fumei]

no doubt using Format is much better. I did mention that it is only working if there are exactly 9 characters.