B
Bob
Can I copy A1 "Steven Grey" so in B1 it will read "Grey Steven"!
--
Thanks in advance for your help....Bob Vance
..
..
..
..
--
Thanks in advance for your help....Bob Vance
..
..
..
..
N
Norman Harker
Hi Bob!
You'll need the following formula in B1:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
You'll need the following formula in B1:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob
Great thanks can it be slightly altered so as if there is only one word it
will read Investment as investment and if blank I don't get the #Value!
fault.
--
Thanks in advance for your help....Bob Vance
..
..
..
..
will read Investment as investment and if blank I don't get the #Value!
fault.
--
Thanks in advance for your help....Bob Vance
..
..
..
..
B
Bob
Ah I just noticed that if there is a "Mr Steven Grey" it doesn't reverse it!
--
Thanks in advance for your help....Bob Vance
..
..
..
..
--
Thanks in advance for your help....Bob Vance
..
..
..
..
N
Norman Harker
Hi Bob!
Try:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND("
",A1)-1)))
The First IF handles empty cells, the second covers single words.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Try:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND("
",A1)-1)))
The First IF handles empty cells, the second covers single words.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
N
Norman Harker
Hi Bob!
I'm about to disappear to the place that thinks I work for them. But
how do you want Mr Steven Grey
Grey Mr Steven
or
Grey Steven Mr
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
I'm about to disappear to the place that thinks I work for them. But
how do you want Mr Steven Grey
Grey Mr Steven
or
Grey Steven Mr
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob
Its not turning it around 
--
Thanks in advance for your help....Bob Vance
..
..
..
..
--
Thanks in advance for your help....Bob Vance
..
..
..
..
B
Bob
Grey Steven Mr , Thanks
--
Thanks in advance for your help....Bob Vance
..
..
..
..
--
Thanks in advance for your help....Bob Vance
..
..
..
..
N
Norman Harker
Hi Bob!
Now try:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND("
",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1))))&" "&LEFT(RIGHT(A1,LEN(A1)-FIND("
",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)&" "&LEFT(A1,FIND("
",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))))
Best to copy from above and paste into the formula bar.
It seems to cover all you have (until now <vbg>)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Now try:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND("
",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1))))&" "&LEFT(RIGHT(A1,LEN(A1)-FIND("
",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)&" "&LEFT(A1,FIND("
",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))))
Best to copy from above and paste into the formula bar.
It seems to cover all you have (until now <vbg>)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
R
RagDyer
Hey Norman,
How about Mr. Gerald Mac Pherson?<vbg>
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
Hi Bob!
Now try:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND("
",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1))))&" "&LEFT(RIGHT(A1,LEN(A1)-FIND("
",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)&" "&LEFT(A1,FIND("
",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))))
Best to copy from above and paste into the formula bar.
It seems to cover all you have (until now <vbg>)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
How about Mr. Gerald Mac Pherson?<vbg>
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
Hi Bob!
Now try:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND("
",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1))))&" "&LEFT(RIGHT(A1,LEN(A1)-FIND("
",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)&" "&LEFT(A1,FIND("
",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))))
Best to copy from above and paste into the formula bar.
It seems to cover all you have (until now <vbg>)
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob
Its just coping what is in A1 in the same order
--
Thanks in advance for your help....Bob Vance
..
..
..
..
--
Thanks in advance for your help....Bob Vance
..
..
..
..
N
Norman Harker
Hi RD!
How about you.....
The Mr. written properly with a dot would make like easier. Mac Pherson is
probably a mis-spelling. I was anticipating two given names or post-nominals
as the next request and that was was going to be met with VBA or helper
columns (needles to say, I used a helper column to build it).
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
How about you.....
The Mr. written properly with a dot would make like easier. Mac Pherson is
probably a mis-spelling. I was anticipating two given names or post-nominals
as the next request and that was was going to be met with VBA or helper
columns (needles to say, I used a helper column to build it).
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob
Norman just not getting this to work its just straight coping A1
--
Thanks in advance for your help....Bob Vance
..
..
..
..
--
Thanks in advance for your help....Bob Vance
..
..
..
..
N
Norman Harker
Hi Bob!
It's a question of how the copy and paste is playing around with the
formula.
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("
",A1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1))))&"
"&LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("
",A1)))-1)&" "&LEFT(A1,FIND(" ",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))&"
"&LEFT(A1,FIND(" ",A1)-1))))
Try this approach:
Copy this formula into the formula bar:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))=0,A1,IF(LEN(A1)-LEN(SUBST
ITUTE(A1,"_",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1
)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1))))&"_"&LEFT(RIGHT(A1
,LEN(A1)-FIND("_",A1)),FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))-1)&"
"&LEFT(A1,FIND("_",A1)-1),RIGHT(A1,LEN(A1)-FIND("_",A1))&"_"&LEFT(A1,FIND("_
",A1)-1))))
Now edit the formula. Every time you see "_" or " _" replace it with " "
(double quote-space-double quote)
For explanation. In the SUBSTITUTE functions I'm replacing " " with "". In
the FIND functions, I'm finding the " ".
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
It's a question of how the copy and paste is playing around with the
formula.
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("
",A1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1))))&"
"&LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("
",A1)))-1)&" "&LEFT(A1,FIND(" ",A1)-1),RIGHT(A1,LEN(A1)-FIND(" ",A1))&"
"&LEFT(A1,FIND(" ",A1)-1))))
Try this approach:
Copy this formula into the formula bar:
=IF(A1="","",IF(LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))=0,A1,IF(LEN(A1)-LEN(SUBST
ITUTE(A1,"_",""))=2,RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1
)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1))))&"_"&LEFT(RIGHT(A1
,LEN(A1)-FIND("_",A1)),FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))-1)&"
"&LEFT(A1,FIND("_",A1)-1),RIGHT(A1,LEN(A1)-FIND("_",A1))&"_"&LEFT(A1,FIND("_
",A1)-1))))
Now edit the formula. Every time you see "_" or " _" replace it with " "
(double quote-space-double quote)
For explanation. In the SUBSTITUTE functions I'm replacing " " with "". In
the FIND functions, I'm finding the " ".
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
B
Bob
Thanks Norman but I just cant get it to work, I will look for a template I
remember seeing somewhere for last names first, Thanks
--
Thanks in advance for your help....Bob Vance
..
..
..
..
remember seeing somewhere for last names first, Thanks
--
Thanks in advance for your help....Bob Vance
..
..
..
..
B
Bob
Found this which works must have one comer in A1 to separate
=RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(",",A1)-1))-2)&"
"&LEFT(A1,FIND(",",A1)-1)
--
Thanks in advance for your help....Bob Vance
..
..
..
..
=RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(",",A1)-1))-2)&"
"&LEFT(A1,FIND(",",A1)-1)
--
Thanks in advance for your help....Bob Vance
..
..
..
..
B
Bob
Now that its done I will tell you why:
Drop down box now is now alphabet order from last names , but cant post them
like that so name define in to next cells that is in Letter label format and
address, and ha the first cell is in white font so we only get to see the
proper name and address below, make sense?
--
Thanks in advance for your help....Bob Vance
..
..
..
..
Drop down box now is now alphabet order from last names , but cant post them
like that so name define in to next cells that is in Letter label format and
address, and ha the first cell is in white font so we only get to see the
proper name and address below, make sense?
--
Thanks in advance for your help....Bob Vance
..
..
..
..