Insert a space in a text string ina range of cells

i have several columms with a string of text in eeach i need to insert a space between the 3rd and 4th character of the string example:
current txt: ABCDEFG
new txt: ABC DEFG

Is there a formula that can easily do this and how can i save it as a template on the sheet so that i dont have to create the formula all the time just update the data
gd6627Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Sure:

=IF(LEN(A2)>3,LEFT(A2,3)&" "&MID(A2,4,LEN(A2)),A2)

That returns an modified value if the length is <= 3.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Just as an alternative:
=TRIM(REPLACE(A2,4,1," "&MID(A2,4,1)))
0
gd6627Author Commented:
Ok Thank you , how bout as far as keeping the formula in the cell after i delete the data is there a way to make the formula stay on the worksheet with out recreating it all the time
0
gd6627Author Commented:
Am getting a circular reference warning when i try to paste the formula
0
Rory ArchibaldCommented:
Both formulas assume you are not putting them in A2
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.