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

[Guest]

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 Petus]

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

HTH

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

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

HTH
--
AP

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 Petus]

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

Cheers,
--
AP

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)

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

HTH
--
AP

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!

 

[Bob Phillips]

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

Bob

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

Cheers,
--
AP

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)

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 (e-mail address removed)...
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!

 

[Marti]

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

Thanks, Marti

 

[MattShoreson]

Yes. Use the same logic as daddylonglegs uses. Search for the space in
the string and return using the relative lengths and positions of the
forename and surname.

 

[Ardus Petus]

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

HTH

 

[MattShoreson]

or you could have used...

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

 

[Ardus Petus]

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

Cheers,

 

[Marti]

Thanks so much! This is like magic!

Marti