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

Thanks


--
stapleton2308
------------------------------------------------------------------------
stapleton2308's Profile: http://www.excelforum.com/member.php...o&userid=30878
View this thread: http://www.excelforum.com/showthread...hreadid=512688

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.

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

"stapleton2308" wrote:


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

Thanks


--
stapleton2308
------------------------------------------------------------------------
stapleton2308's Profile: http://www.excelforum.com/member.php...o&userid=30878
View this thread: http://www.excelforum.com/showthread...hreadid=512688

I'd use the edit|replace, too.

But one more option:

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



stapleton2308 wrote:

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

Thanks

--
stapleton2308
------------------------------------------------------------------------
stapleton2308's Profile: http://www.excelforum.com/member.php...o&userid=30878
View this thread: http://www.excelforum.com/showthread...hreadid=512688

--

Dave Peterson

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