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?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.