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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
something like...

=substitute(a1," ","")

copy, past as values when done.

HTH
 
Upvote 0
Thats very succint...

I was thinking Text to columns then concatenate.

But thats very brief...

Nice one !!!

Ta

(y)
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top