Remove gaps

Nilam

Board Regular
Joined
Feb 12, 2004
Messages
58
I have a column of postcodes in the following format:

X22 6YG

I have 2107 rows of postcodes. What I want to do is get rid of the gap in all the postcodes (so the e.g. above would read X226YG). How can this be done?

Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
something like...

=substitute(a1," ","")

copy, past as values when done.

HTH
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Thats very succint...

I was thinking Text to columns then concatenate.

But thats very brief...

Nice one !!!

Ta

(y)
 

leibale

Active Member
Joined
Sep 12, 2002
Messages
336
use Edit Replace :
replace one space with nothing after selecting the range
or
make a new column with formulaL left(cell,3)&right(cell,3)
 

Nilam

Board Regular
Joined
Feb 12, 2004
Messages
58
Thanks firefytr! Thats exactly what i was looking for

:wink:
 

Blubo

New Member
Joined
Jul 4, 2010
Messages
2
Yeah that was just what i needed to sort out my problem! (Concatenation)

By using it to pull together data in my spreadsheet I was able to create the text to make vcf contact cards using excel.

My scenario:
I have a load of new students starting this semester, their details all in xls..
By using this feature I can quickly convert this xls into multiple individual vcfs

Brilliant. Thank you.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,542
Members
425,480
Latest member
br400821

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