Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not have any
spaces.
I can do it manually but with over 400 rows its a pain - could a macro or
perhaps a VBA function do it automatically.

Any help appreciated

Cheers

Mully

Try

=TRIM(A2)&","&TRIM(B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not have any
spaces.
I can do it manually but with over 400 rows its a pain - could a macro or
perhaps a VBA function do it automatically.

Any help appreciated

Cheers

Mully

mully wrote:

Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not have any
spaces.
I can do it manually but with over 400 rows its a pain - could a macro or
perhaps a VBA function do it automatically.

In cell D2, enter "=TRIM(A2) & TRIM(B2)"

Then copy & paste down as far as you need.


--
Gordon Rainsford

London UK

Hi Mully,

How are you building D2? If you do D2 = TRIM(A2&B2) you should not get
any spaces. You may not need a macro at all!!!

Regards,

Ric

That will only trim the resultant value, not each individual value.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ric_deez" wrote in message
oups.com...
Hi Mully,

How are you building D2? If you do D2 = TRIM(A2&B2) you should not get
any spaces. You may not need a macro at all!!!

Regards,

Ric

Thank You Gentlemen

Problem Solved --- thank goodness for some very intelligent help people.

Cheers

Mully

"Bob Phillips" wrote:

Try

=TRIM(A2)&","&TRIM(B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not have any
spaces.
I can do it manually but with over 400 rows its a pain - could a macro or
perhaps a VBA function do it automatically.

Any help appreciated

Cheers

Mully

Hi All

About 3/4 Hr ago I sent an Email using your solution a shortened example below

4100,WM10677
Below is what I received back
4100,WM10677 ,262,1398

Is it possible that on cut and paste in to Excel Column E that the two
WM10677 Code Numbers would automatically match on the same row.in Columns B &
E.

I know what I want to happen but on reading the above appears crazy!!!

Cheers

Mully



"mully" wrote:

Thank You Gentlemen

Problem Solved --- thank goodness for some very intelligent help people.

Cheers

Mully

"Bob Phillips" wrote:

Try

=TRIM(A2)&","&TRIM(B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not have any
spaces.
I can do it manually but with over 400 rows its a pain - could a macro or
perhaps a VBA function do it automatically.

Any help appreciated

Cheers

Mully

Don't understand. Can you give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

About 3/4 Hr ago I sent an Email using your solution a shortened example
below

4100,WM10677
Below is what I received back
4100,WM10677 ,262,1398

Is it possible that on cut and paste in to Excel Column E that the two
WM10677 Code Numbers would automatically match on the same row.in Columns
B &
E.

I know what I want to happen but on reading the above appears crazy!!!

Cheers

Mully



"mully" wrote:

Thank You Gentlemen

Problem Solved --- thank goodness for some very intelligent help people.

Cheers

Mully

"Bob Phillips" wrote:

Try

=TRIM(A2)&","&TRIM(B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not
have any
spaces.
I can do it manually but with over 400 rows its a pain - could a
macro or
perhaps a VBA function do it automatically.

Any help appreciated

Cheers

Mully

"Bob Phillips" wrote:

Don't understand. Can you give an example?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

About 3/4 Hr ago I sent an Email using your solution a shortened example
below

4100,WM10677
Below is what I received back
4100,WM10677 ,262,1398
Hi Bob

Another clanger dropped should have told you I receive back an attachment in
Notebook. I send in an Email 4100,WM10677 -- I then receive back
4100,WM10677 ,262,1398 --- the last two sets of figures are Miles and
Yards. What I've been doing is cutting 4100,WM10677 ,262,1398 out of the
attachment and pasting in Column E that is then converted Text To Columns no
problem with that. The problem is that when I send the list and its long --
sometimes when they return it some are missing so it doesn't cut and paste
exactly where it should. I was wondering if the 4100,WM10677 that I send and
is always in the same Column D - Cell and Row that on pasting
4100,WM10677,262,1398 into Column E that it would search and end up on the
same row.

Does that make sense???

4100,WM10677 4100,WM10677 ,262,1398 4100 WM10677 262 1398
Col D Col E Col G
Col H Col I Col J


Cheers

Mully



Is it possible that on cut and paste in to Excel Column E that the two
WM10677 Code Numbers would automatically match on the same row.in Columns
B &
E.

I know what I want to happen but on reading the above appears crazy!!!

Cheers

Mully



"mully" wrote:

Thank You Gentlemen

Problem Solved --- thank goodness for some very intelligent help people.

Cheers

Mully

"Bob Phillips" wrote:

Try

=TRIM(A2)&","&TRIM(B2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mully" wrote in message
...
Hi All

On a worksheet
Cell A2 = 4100,
Cell B2 = WM0001
CellD2 when using Text to Columns = 4100, WM0001
how do I get rid of the space so that Cell D2 = 4100,WM0001
I Email over 400 rows of similar information and Cell D2 must not
have any
spaces.
I can do it manually but with over 400 rows its a pain - could a
macro or
perhaps a VBA function do it automatically.

Any help appreciated

Cheers

Mully

Hi Bob,

Maybe it is getting late in the day in my corner of the woods but I
fail to see how trim(A&B) and trim(A)&trim(B) would not always return
the same result, afterall all we are doing is getting rid of the
spaces... If we were talking about LTRIM, then I would understand...

Ric

Ric,

Read the Help on TRIM, it says ... Removes all spaces from text except for
single spaces between words ... Thus, if either A1 or B1 has a trailing or
leading space, it creates a single space in the A1&B1, which doesn't get
trimmed. Or

A1: Bobspace
B1:space Ric

=TRIM(A1&B1) = BobspaceRic
=TRIM(A1)&TRIM(B1) = BobRic

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ric_deez" wrote in message
ups.com...
Hi Bob,

Maybe it is getting late in the day in my corner of the woods but I
fail to see how trim(A&B) and trim(A)&trim(B) would not always return
the same result, afterall all we are doing is getting rid of the
spaces... If we were talking about LTRIM, then I would understand...

Ric

Hi Bob,

Thanks for clarifying this!

Ric