Space after every 3 characters in excel

[excelinexcel7]

Hi Everyone,

I have a list of 1000 columns where the data is like this:

ABCDEFGHIJKL
DCABHG
ABTGYU

I want to add a space after every 3 characters. Please advise, how can I do this in excel.

So the output should be like this.

ABC DEF GHI JKL
DCA BHG
ABT GYU



Thanks
Jay

 

[CharlesChuckieCharles]

Use the standard Find/Replace

Find what ??? (just three ? representing three chars)
replace with ??? (now three ? plus a space at the end)

? is a place holder for any character

Try it it works

 

[excelinexcel7]

Thanks Charles, but this doesn't work because the characters are random, so I don't really know what to Find.

 

[miss_ell]

Hello,

If the text strings aren't too long, you can use this formula if J16 has ABCDEFGHIJKL:
=CONCATENATE(MID(J16,1,3)," ",MID(J16,4,3)," ",MID(J16,7,3)," ",MID(J16,10,3)," ",MID(J16,13,3)," ") etc., etc., etc......

which can be expanded if necessary.

 

[CharlesChuckieCharles]

Thanks Charles, but this doesn't work because the characters are random, so I don't really know what to Find.

instead of putting in ABC or 2ER you actually enter three question marks ??? in the find and question marks ??? plus a space in the replace

therefore excel wil replace any three consectutive characters with those same characters plus a space at the end TRY IT IT WORKS

 

[miss_ell]

The thing is, it actually replaces the three characters with ???.

So, using the find and replace method on ABCDEFG, this is replaced by ??? ??? ?.

Unless I am doing something wrong?

 

[Special-K99]

I didnt wanna put Charles off but Im getting this too.

 

[excelinexcel7]

You are right guys, it replaces the three characters with ??? which I don't want. The result should be like
ABC DEF GHI JKL
DCA BHG
ABT GYU

 

[excelinexcel7]

This worked.

=CONCATENATE(MID(A2,1,3)," ",MID(A2,4,3)," ",MID(A2,7,3)," ",MID(A2,10,3)," ",MID(A2,13,3)," ",MID(A2,16,3)," ",MID(A2,19,3)," ",MID(A2,22,3)," ",MID(A2,25,3)," ",MID(A2,28,3)," ",MID(A2,31,3)," ",MID(A2,34,3)," ",MID(A2,37,3)," ",MID(A2,40,3)," ",MID(A2,43,3))

Thanks a lot miss_ell


Regards
Jay

 

[Gopalakrishnan]

Thanks guys,,,,, this post has helped me in time!