add space between numbers

[love2waltz]

I need a macro to add a space between numbers in col A. Each number in col A is always 13 characters in length. I need to insert a space after character 9 and another space after character 12. The original number is in this format xxxxxxxxxxxxx and I need all rows in col A to be in this format xxxxxxxxx xxx x. Any help you can give is greatly appreciated, thank you.

[Andrew-R]

Is this a one-off job, or something you have to do regularly?

Also, are all of the values in col A numeric? If so you can do this just using a custom format.

[Søren Larsen]

You could do it without a macro:

=LEFT(A1,9)&" "&MID(A1,10,3)&" "&RIGHT(A18,3)

[StevenM]

Re: format xxxxxxxxx xxx xx
9 + 3 + 2 = 14 and not 15

Please Login or Register  to view this content.

[love2waltz]

I do this about once a month, oh, I made a mistake in my original post, I've since corrected it. Thanks for your help.

[love2waltz]

Hi soren,

I understand the left (a1,9) and the cancatenation to add the spaces, but I don't understand the 3 in mid functin and the 18 and 3 in the right function. could you please explain? Thank you,

[Cutter]

The A18 in his suggestion is a typo - he meant A1
The 3 is the number of characters to pull from A1 (starting with the 10th character)
http://www.excelfunctions.net/MidFunction.html

[Søren Larsen]

The A18 in his suggestion is a typo - he meant A1

Ahh, yes. Thanks Cutter; it should have looked like this:

=LEFT(A1,9)&" "&MID(A1,10,3)&" "&RIGHT(A1,1) where you replace A1 with the first of you cells containing the string, and then copy it down. You could then copy/paste-values on top of your original data, or use the new string for the following computations.

[jindon]

Or

=REPLACE(REPLACE(A1,10,," "),14,," ")