Concatenate Macro

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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