Inserting space in between the conents in a cell

[bengaluru]

I have to split the contents of a cell (over 1200 cells) by inserting a space in between

My data is M4N2XC .....this needs to be like this M4N 2XC. Data in the subsequent cells are similar but not the same.

I read somewhere that I can use the following formula

=left(a1,2)&" "&right(a1,4)

Can somone tell me how to use this formula - where do I put this formula.

[NBVC]

for your case it would be:

=left(a1,3)&" "&right(a1,3)

so the formula assumes the original data is in cell A1. It takes the left 3 characters, adds a space and then the right 3 characters.

the formula goes in any cell in a free column, preferably in same row, then you can copy the formula down the column, by clicking and dragging on the little black square at bottom right corner of cell with the first formula in it...

you can then copy this whole column and go to original column, and go to Edit|Paste Special and select Values.

then you can delete the formula column, if desired.

[bengaluru]

Thank you so much, my friend. You guys are the best and I can count on you anytime.

Take care

for your case it would be:

=left(a1,3)&" "&right(a1,3)

so the formula assumes the original data is in cell A1. It takes the left 3 characters, adds a space and then the right 3 characters.

the formula goes in any cell in a free column, preferably in same row, then you can copy the formula down the column, by clicking and dragging on the little black square at bottom right corner of cell with the first formula in it...

you can then copy this whole column and go to original column, and go to Edit|Paste Special and select Values.

then you can delete the formula column, if desired.