Is this Possible? A bit of a brain teaser if you ask me.

I am trying to creat a formula that allows me to change say Smith, John to
John Smith. However, It must be done using the LEFT, RIGHT, TRIM, LEN, and
SEARCH functions only. The kicker is that I need it to transfer from one cell
to another, so CELL:A1 would appear like this: [Smith, John]
And Cell:A2 would appear like this: [John Smith]
Is this possible? A friend says yes but I am yet so find a solution. I
appreciate all replies....I have only been using Excel for about 2 days so I
am a newbie. Thanks A Lot!

One way:
=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)

HTH
--
AP

"Levi" <[email protected]> a écrit dans le message de news:
[email protected]...
>I am trying to creat a formula that allows me to change say Smith, John to
> John Smith. However, It must be done using the LEFT, RIGHT, TRIM, LEN, and
> SEARCH functions only. The kicker is that I need it to transfer from one
> cell
> to another, so CELL:A1 would appear like this: [Smith, John]
> And Cell:A2 would appear like this: [John Smith]
> Is this possible? A friend says yes but I am yet so find a solution. I
> appreciate all replies....I have only been using Excel for about 2 days so
> I
> am a newbie. Thanks A Lot!

You got two responses to this question yesterday, the one from daddylonglegs
being especially good IMO. What is wrong with these suggestions?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Levi" <[email protected]> wrote in message
news:[email protected]...
> I am trying to creat a formula that allows me to change say Smith, John to
> John Smith. However, It must be done using the LEFT, RIGHT, TRIM, LEN, and
> SEARCH functions only. The kicker is that I need it to transfer from one
cell
> to another, so CELL:A1 would appear like this: [Smith, John]
> And Cell:A2 would appear like this: [John Smith]
> Is this possible? A friend says yes but I am yet so find a solution. I
> appreciate all replies....I have only been using Excel for about 2 days so
I
> am a newbie. Thanks A Lot!

Ardus,

That leaves the comma in which I don't think the OP wanted, but more
relevantly, it is a very pedantic formula, just like my effort yesterday
<vbg>. Look at this that daddylonglegs came up,

=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)

somewhat neater wouldn't you say?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> One way:
> =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)
>
> HTH
> --
> AP
>
> "Levi" <[email protected]> a écrit dans le message de news:
> [email protected]...
> >I am trying to creat a formula that allows me to change say Smith, John
to
> > John Smith. However, It must be done using the LEFT, RIGHT, TRIM, LEN,
and
> > SEARCH functions only. The kicker is that I need it to transfer from one
> > cell
> > to another, so CELL:A1 would appear like this: [Smith, John]
> > And Cell:A2 would appear like this: [John Smith]
> > Is this possible? A friend says yes but I am yet so find a solution. I
> > appreciate all replies....I have only been using Excel for about 2 days
so
> > I
> > am a newbie. Thanks A Lot!
>
>

I misread OP's question and produced a VERY pedantic solution!

Cheers,
--
AP

"Bob Phillips" <[email protected]> a écrit dans le message
de news: [email protected]...
> Ardus,
>
> That leaves the comma in which I don't think the OP wanted, but more
> relevantly, it is a very pedantic formula, just like my effort yesterday
> <vbg>. Look at this that daddylonglegs came up,
>
> =MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)
>
> somewhat neater wouldn't you say?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
>> One way:
>> =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)
>>
>> HTH
>> --
>> AP
>>
>> "Levi" <[email protected]> a écrit dans le message de news:
>> [email protected]...
>> >I am trying to creat a formula that allows me to change say Smith, John
> to
>> > John Smith. However, It must be done using the LEFT, RIGHT, TRIM, LEN,
> and
>> > SEARCH functions only. The kicker is that I need it to transfer from
>> > one
>> > cell
>> > to another, so CELL:A1 would appear like this: [Smith, John]
>> > And Cell:A2 would appear like this: [John Smith]
>> > Is this possible? A friend says yes but I am yet so find a solution. I
>> > appreciate all replies....I have only been using Excel for about 2 days
> so
>> > I
>> > am a newbie. Thanks A Lot!
>>
>>
>
>

Don't be hard on yourself, so did I <G>

Bob

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> I misread OP's question and produced a VERY pedantic solution!
>
> Cheers,
> --
> AP
>
> "Bob Phillips" <[email protected]> a écrit dans le message
> de news: [email protected]...
> > Ardus,
> >
> > That leaves the comma in which I don't think the OP wanted, but more
> > relevantly, it is a very pedantic formula, just like my effort yesterday
> > <vbg>. Look at this that daddylonglegs came up,
> >
> > =MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)
> >
> > somewhat neater wouldn't you say?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:[email protected]...
> >> One way:
> >> =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)
> >>
> >> HTH
> >> --
> >> AP
> >>
> >> "Levi" <[email protected]> a écrit dans le message de
news:
> >> [email protected]...
> >> >I am trying to creat a formula that allows me to change say Smith,
John
> > to
> >> > John Smith. However, It must be done using the LEFT, RIGHT, TRIM,
LEN,
> > and
> >> > SEARCH functions only. The kicker is that I need it to transfer from
> >> > one
> >> > cell
> >> > to another, so CELL:A1 would appear like this: [Smith, John]
> >> > And Cell:A2 would appear like this: [John Smith]
> >> > Is this possible? A friend says yes but I am yet so find a solution.
I
> >> > appreciate all replies....I have only been using Excel for about 2
days
> > so
> >> > I
> >> > am a newbie. Thanks A Lot!
> >>
> >>
> >
> >
>
>

This is great! Is it possible to do the reverse, change Joe Smith
into Smith, Joe?

Thanks, Marti

On Tue, 2 May 2006 10:16:35 +0100, "Bob Phillips"
<[email protected]> wrote:

>Ardus,
>
>That leaves the comma in which I don't think the OP wanted, but more
>relevantly, it is a very pedantic formula, just like my effort yesterday
><vbg>. Look at this that daddylonglegs came up,
>
>=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)
>
>somewhat neater wouldn't you say?

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&", "&LEFT(A1,SEARCH(" ",A1)-1)

HTH
--
AP

"Marti" <[email protected]> a écrit dans le message de news:
[email protected]...
> This is great! Is it possible to do the reverse, change Joe Smith
> into Smith, Joe?
>
> Thanks, Marti
>
> On Tue, 2 May 2006 10:16:35 +0100, "Bob Phillips"
> <[email protected]> wrote:
>
>>Ardus,
>>
>>That leaves the comma in which I don't think the OP wanted, but more
>>relevantly, it is a very pedantic formula, just like my effort yesterday
>><vbg>. Look at this that daddylonglegs came up,
>>
>>=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)
>>
>>somewhat neater wouldn't you say?

or you could have used...

=MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1)

You already knew I'm a brain-twister (unknowing of anybody elses's
solutions)

Cheers,
--
AP

"MattShoreson" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> or you could have used...
>
> =MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1)
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile:
> http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=537972
>

Thanks so much! This is like magic!

Marti

On Tue, 2 May 2006 15:50:43 +0200, "Ardus Petus"
<[email protected]> wrote:

>=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&", "&LEFT(A1,SEARCH(" ",A1)-1)