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

onyour6

New Member
Joined
Sep 30, 2006
Messages
4
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Sep 30, 2006
Messages
4
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

onyour6

New Member
Joined
Sep 30, 2006
Messages
4

ADVERTISEMENT

It worked

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

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

New Member
Joined
Sep 30, 2006
Messages
4
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?
 

Forum statistics

Threads
1,141,629
Messages
5,707,506
Members
421,511
Latest member
mgroah1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top