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

[onyour6]

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

 

[barry houdini]

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

 

[onyour6]

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

 

[barry houdini]

Does this example help?

Book2
	A	B	C	D
1		old	new
2		abcdef	abc def
3		xyz123	xyz 123
4		j3l8x2	j3l 8x2
5		zzzzzz	zzz zzz
6		222222	222 222
7		2bon2b	2bo 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

 

[onyour6]

It worked

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

 

[larrydunn]

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.

 

[onyour6]

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?