I have a spreadsheet that has a column of postal codes. The format that is shown now as an example is L7G 5Y3. However, the format I need for Canada Post is L7G5Y3 (so to remove the space between the first 3 characters with the last 3 characters. Is there a way that I can do this without having to go thru 900 lines of this document and manually remove the space between the characters?
To save time and manual entry of long number files. In this case 995 lines would need to be changed removing the space between the first and last three characters.
—–
Michelle Vata
I can think of two quick ways to do this.
The first is to create a new column next to the existing column. In that column use the SUBSTITUTE function to remove the spaces to get new values.
So if this is column D, then in E2 put =SUBSTITUTE(D2, " ","").
That replaces any space with nothing.
Put that in all cells in column E
Then you can copy column E, and paste into D using Edit, Paste Formula Results. Then remove column E since you don't need it anymore.
Or, you could just copy this column of zip codes to a new document that has nothing else. Then use Edit, Find to find and replace all spaces with nothing. Then copy and paste back into the original document.
Hi Gary,
I am not following. The following is not working, =SUBSTITUTE(A36,(B36,",")). OR SUBSTITUTE(A36,B36,",")
I also tried the 2nd option and this doesn't work also.
Michelle: The formula is:
=SUBSTITUTE(D2, " ","")
Make sure you type it, using straight double-quotes.
Look at the SUBSTITUTE function description. The first item is the source cell, the second is the text to search for (a space) the third is nothing (quotes with nothing in between).
As for the second option. it definitely works. What happened when you tried? Were you searching for a space and replacing with nothing?
Thanks, Gary, I wasn't putting the correct substitute formula into the spreadsheet. It works now and wow what a time saver. Thank you so much.
Ok so the first option does not work. I cannot have 2 of the same column. One with the space and another without the space. So when I delete the first column then the format does not save.
I had to go to option 2. So what happens is the find and replace does open.
I type /all spaces with nothing. But there is nothing I can put into find and none of the buttons below like Replace All or Replace & find or Replace become active.
MichelleVata: You don't need to have two of the same column. Read my initial response carefully: "Then you can copy column E, and paste into D using Edit, Paste Formula Results. Then remove column E since you don't need it anymore."
When you do a Find, just type a space with the spacebar. That's all that should be in the Find field, just one space.
Hey Gary, I am so sorry. I am sure you are saying it properly, it's just I am not understanding. I did a video of what I am seeing that I posted on youtube. https://youtu.be/mV8KElPC_fY to show you what I am doing. If you can look at that it might help with what I am not doing correctly.
OK I got it LOL
I missed the paste formula results.
Thanks for your help and patience :)
Michelle: OK, so let's break it down and I'll use the columns you are using.
1. "Then you can copy column M"
So select all of the cells in column M. A quick way to do this is to double-click on "M" at the top of the column. Then use Edit, Copy or Command+C.
2. "and paste into L using Edit, Paste Formula Results"
So select all of the cells in Column L. Just double-click on "L" at the top of the column to select all of the cells. Then go to Edit, Paste Formula Results.
3. "Then remove column M"
So select column M (click once on the "M" at the top). Then choose Table, Delete Column.
For the second idea, realize this is SEPARATE from the above idea. Two different things. Two different ways of doing it.
So you would be starting with just column L with your original data. You never created column M.
Copy all of column L. Paste into a column in a new document.
Then do Edit, Find, Find.
Switch to Find and Replace with the pulldown menu.
In the first field, type just a space with the spacebar.
In the second field, leave it EMPTY. Not the words "all spaces with nothing" -- just leave it empty, blank, devoid of anything at all.
Then do the Replace All.