Trim

[stapleton2308]

Hi,

If i have data which in one cell which looks like below:

ABC 0001
DDS 0002
HHH 0003
EEI 0004

(each of these has once space between the letters and numbers)

How can i combine it, so the cells now looks like:

ABC0001
DDS0002
HHH0003
EEI0004

Help would be much appreciated

Thank

 

[Dave O]

The easiest way is to highlight the column and click >Edit >Replace.
In the resulting "Find What" box type a space, and leave the "Replace
with" box blank.

 

[Gizmo63]

To make a permanent change use the edit-replace funtion.
In the 'find what' field put in a single space, leave the replace field
empty, hit replace all and BYU!

To solve this in another column and assuming a constant format of 3
characters, a a space and 4 characters as per example try this formula where
A1 contains the data.

=left(a1,3)&right(a1,4)

If the space moves around then:
=left(a1,find(" ",a1,1)-1)&right(A1,len(a1)-find(" ",a1,1))

hth

Giz

 

[Dave Peterson]

I'd use the edit|replace, too.

But one more option:

=substitute(a1&b1," ","")

 

[Robert_Steel]

Alternatively as a formula option
if the structure is fixed
=REPLACE(M1,4,1,"")

or if the space moves about
=REPLACE(M1,FIND(" ",M1),1,"")

Cheers RES