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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
One way:
=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)

HTH
 
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)
 
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 said:
One way:
=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)

HTH
--
AP

Levi said:
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!
Click to expand...
Click to expand...
 
I misread OP's question and produced a VERY pedantic solution!

Cheers,
--
AP

Bob Phillips said:
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 said:
One way:
=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))&", "&LEFT(A1,FIND(",",A1)-1)

HTH
--
AP

Levi said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Don't be hard on yourself, so did I <G>

Bob

Ardus Petus said:
I misread OP's question and produced a VERY pedantic solution!

Cheers,
--
AP

Bob Phillips said:
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 said:
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,
Click to expand...
John
to
John Smith. However, It must be done using the LEFT, RIGHT, TRIM,
Click to expand...
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
Click to expand...
days
so
I
am a newbie. Thanks A Lot!
Click to expand...
Click to expand...
Click to expand...
 
This is great! Is it possible to do the reverse, change Joe Smith
into Smith, Joe?

Thanks, Marti
 
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.
 
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&", "&LEFT(A1,SEARCH(" ",A1)-1)

HTH
 
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,
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Comparing two columns 1
Filter out rows based on the value of a cell 4
Separating a comma separated list 3
why won't text-to-columns work here? 6
Comparing data from one column with data within another column 6
getting one of each 3
Macro to complete Google search based on a cell? 0
Report formatting problem 1

Back
Top