software

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: www.excelforum.com/member.php...oamp;userid=30878
View this thread: www.excelforum.com/showthread...hreadid=512688The easiest way is to highlight the column and click gt;Edit gt;Replace.
In the resulting quot;Find Whatquot; box type a space, and leave the quot;Replace
withquot; 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)amp;right(a1,4)

If the space moves around then:
=left(a1,find(quot; quot;,a1,1)-1)amp;right(A1,len(a1)-find(quot; quot;,a1,1))

hth

Giz

quot;stapleton2308quot; wrote:

gt;
gt; Hi,
gt;
gt; If i have data which in one cell which looks like below:
gt;
gt; ABC 0001
gt; DDS 0002
gt; HHH 0003
gt; EEI 0004
gt;
gt; (each of these has once space between the letters and numbers)
gt;
gt; How can i combine it, so the cells now looks like:
gt;
gt; ABC0001
gt; DDS0002
gt; HHH0003
gt; EEI0004
gt;
gt; Help would be much appreciated
gt;
gt; Thanks
gt;
gt;
gt; --
gt; stapleton2308
gt; ------------------------------------------------------------------------
gt; stapleton2308's Profile: www.excelforum.com/member.php...oamp;userid=30878
gt; View this thread: www.excelforum.com/showthread...hreadid=512688
gt;
gt;

I'd use the edit|replace, too.

But one more option:

=substitute(a1amp;b1,quot; quot;,quot;quot;)
stapleton2308 wrote:
gt;
gt; Hi,
gt;
gt; If i have data which in one cell which looks like below:
gt;
gt; ABC 0001
gt; DDS 0002
gt; HHH 0003
gt; EEI 0004
gt;
gt; (each of these has once space between the letters and numbers)
gt;
gt; How can i combine it, so the cells now looks like:
gt;
gt; ABC0001
gt; DDS0002
gt; HHH0003
gt; EEI0004
gt;
gt; Help would be much appreciated
gt;
gt; Thanks
gt;
gt; --
gt; stapleton2308
gt; ------------------------------------------------------------------------
gt; stapleton2308's Profile: www.excelforum.com/member.php...oamp;userid=30878
gt; View this thread: www.excelforum.com/showthread...hreadid=512688

--

Dave Peterson

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

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

Cheers RES