# How do I Insert a space in all rows in a selected colmn?

#### onyour6

##### New Member
I am working with canadian Postal codes w/ hundreds of records that I need to change and Insert a space after the 3rd char.
Example "A1A1A1" should read "1A1 1A1" or "1A1-1A1", I have to do this w/out messing up my Header row ? I had found this code works but...
=LEFT(B12,3) & " " & RIGHT(B12,3)
I need to find a way so that where B12 is stated i need it to be either what i select or a specific column less row 1

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hello, welcome to the board

You can use that formula but a simpler one is

=REPLACE(B2,4,0," ")

which you need to put in row 2 of any blank column, e.g. C and "fill down"

You can then copy the new column and use

Edit > Paste Special > Values to paste it over your original column B

well i Tried that out but it changed everything in my test data file to the same value w/the sapce ... unfortunatly each row in the file is a different client therefore the Postal code in each is different , but thanks though
so i'm still half way there.. 1) need to select my PostalCode column 2) need to insert a space after the 3rd letter in all the rows in each column without changing any of the other info already in each cell

Does this example help?
Book2
ABCD
1oldnew
2abcdefabc def
3xyz123xyz 123
4j3l8x2j3l 8x2
5zzzzzzzzz zzz
6222222222 222
72bon2b2bo n2b
8
Sheet1

If your postcodes are from B2 down put this formula in C2

=REPLACE(B2,4,0," ")

To copy down just select cell C2, put cursor on the bottom right corner of the cell so that a black + shows then double click and formula should automatically replicate down the column [formula in C3 will automatically change to =REPLACE(B3,4,0," ")]

If you do this and all values are the same that probably means that you have calculation set to manual. Press F9 to re-calculate or change to Automatic with

Tools > Options > Calculation

It worked

Thanks it did work ... had to change some settings (office 2007 Beta)

You said:Example "A1A1A1" should read "1A1 1A1" or "1A1-1A1"

I think you meant:Example "1A1A1A" should read "1A1 1A1" or "1A1-1A1"

You might simplify your life by downloading my shareware utilities at excelutilities.com. Once installed, they will easily insert a bit of text at any position in a range of character strings. Select Utilities - Text - Insert Text At Beginning/End Of Line. Tell it to start from the beginning, put in a blank, in 3 characters from beginning, and you're done. If you put it in the wrong position, it's easy to take it out again with the companion utility on the same menu page.

so that part worked, as i can see it is a reference object so I cannt delete the column with the old data in it it or drag and drop the new colmn data ontop of the old one. therefore i would then need to remove my PostalCode header in row one and insert it into row one of the new colmn, in order to keep the data for my word merge doc. is that a safe assumption?

Replies
6
Views
746
Replies
2
Views
317
Replies
3
Views
731
Replies
7
Views
824
Replies
10
Views
773

1,221,064
Messages
6,157,713
Members
451,434
Latest member
VanDookie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back