Removing spaces in numerous cells

M

marti

I have a couple hundred rows of data where I need to change the format.
The format right now is one of these:
"01 01"; "01 02"; "01 03" or
"03AAP"; "03A02"; "03DDP"
I need them to look like this:
"0101"; "0102"; "0103" or
"03AP"; "0302"; "03DP"

Any recommendations? I use Excel 2003
 
P

pogiman via OfficeKB.com

Assuming all data have 5 character, no blank cells and that you only want the
first and last 2 characters:

=Left(cell address,2)&right(cell address,2)

Should you experience problem if the result would be read as a number, use
this:

=if(isnumber(left(cell address,2),text(left(cell address,2),"00"),left(cell
address,2)) &if(isnumber(right(cell address,2),text(right(cell
address,2),"00"),right(cell address,2))
 
A

AnotherNewGuy

Just do a simple replace.

Highlight the rows you want affected.
Click Edit
Click Find and select the Replace tab
In the Find What slot hit the space bar once
In the Replace with slot, do nothing

Select Replace All
 
D

Dave Peterson

If all that data is in a single column, then I'd insert a column directly to the
right of it and use a formula like:

=left(a1,2)&right(a1,2)
And drag down.

This should work if all your strings are 5 characters.
 
S

Shane Devenshire

Suppose your text is in A1 then

=REPLACE(A1,3,1,)

Copy down as necessary.
 
Top