A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Flipping a last name comma first name in the same cell



 
 
Thread Tools Display Modes
  #1  
Old February 29th 08, 08:44 PM posted to microsoft.public.excel.worksheet.functions
Tina Marie
external usenet poster
 
Posts: 4
Default Flipping a last name comma first name in the same cell

Hello ...

Does anyone know how to flip last name and first name

e.g. Robinson, Tina to Tina Robinson in the same cell


--
Thanks!!

T. Marie
Ads
  #2  
Old February 29th 08, 08:52 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,501
Default Flipping a last name comma first name in the same cell

Hi,

If it must be in the same cell then that's a macro so post back but if an
adjacent cell will do try this for a name in a1

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

Mike

"Tina Marie" wrote:

> Hello ...
>
> Does anyone know how to flip last name and first name
>
> e.g. Robinson, Tina to Tina Robinson in the same cell
>
>
> --
> Thanks!!
>
> T. Marie

  #3  
Old February 29th 08, 10:41 PM posted to microsoft.public.excel.worksheet.functions
Conan Kelly
external usenet poster
 
Posts: 419
Default Flipping a last name comma first name in the same cell

Tina Marie,

Then you could copy from the results of the formula Mike H provided, and
paste special as values into the original cell, then delete the formula.

HTH,

Conan




"Mike H" > wrote in message
...
> Hi,
>
> If it must be in the same cell then that's a macro so post back but if an
> adjacent cell will do try this for a name in a1
>
> =MID(A1,SEARCH(" ",A1)+1,LEN(A1))&" "&LEFT(A1,SEARCH(",",A1)-1)
>
> Mike
>
> "Tina Marie" wrote:
>
>> Hello ...
>>
>> Does anyone know how to flip last name and first name
>>
>> e.g. Robinson, Tina to Tina Robinson in the same cell
>>
>>
>> --
>> Thanks!!
>>
>> T. Marie



  #4  
Old May 1st 08, 09:26 PM posted to microsoft.public.excel.worksheet.functions
Raymie Younkin
external usenet poster
 
Posts: 1
Default Flipping Names in same cell

=&RIGHT(C3,LEN(C3)-FIND(",",C3,1))&" " &LEFT(C3,FIND(",",C3,1)-1)&

Where C3 is the cell with the name formatted as

Smith, John
  #5  
Old May 1st 08, 10:34 PM posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
 
Posts: 10,124
Default Flipping Names in same cell

Sub flipnameinsamecell()
For Each c In range("a2:a22")'Selection
x = InStr(c, ",")
c.Value = Right(c, Len(c) - x) & " " & Left(c, x - 1)
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Raymie Younkin" wrote in message ...
> =&RIGHT(C3,LEN(C3)-FIND(",",C3,1))&" " &LEFT(C3,FIND(",",C3,1)-1)&
>
> Where C3 is the cell with the name formatted as
>
> Smith, John

  #6  
Old November 1st 08, 06:26 AM posted to microsoft.public.excel.worksheet.functions
Catherine Damiani
external usenet poster
 
Posts: 1
Default Question about Flipping Names in same cell

say i have John Smith in A1 and want it to be Smith John in A2, what would the formula look like that would be in A2?

thank you!!!
  #7  
Old November 1st 08, 06:47 AM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 1,533
Default Question about Flipping Names in same cell

Hi

This will not work if A1 contains a middlename.

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

Regards,
Per

"Catherine Damiani" skrev i meddelelsen ...
> say i have John Smith in A1 and want it to be Smith John in A2, what would
> the formula look like that would be in A2?
>
> thank you!!!


  #8  
Old November 1st 08, 06:54 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,766
Default Question about Flipping Names in same cell

Hi,

If there is no middle name, then select column A and then Data > Text to
columns. While on delimited, click next and on the next screen, uncheck all
boxes except spaces. Click on finish. Now in a spare column, enter B1&"
"&A1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Catherine Damiani" wrote in message ...
> say i have John Smith in A1 and want it to be Smith John in A2, what would
> the formula look like that would be in A2?
>
> thank you!!!


  #9  
Old November 2nd 08, 12:21 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default Question about Flipping Names in same cell

This will work as long as there is no middle initial or middle name and as
long as the first name is never made up of two names (such as Mary Anne)...

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

--
Rick (MVP - Excel)


"Catherine Damiani" wrote in message ...
> say i have John Smith in A1 and want it to be Smith John in A2, what would
> the formula look like that would be in A2?
>
> thank you!!!


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Flipping text Charlene Excel Worksheet Functions 4 November 15th 07 05:49 PM
Flipping a Spreadsheet Joy Excel Worksheet Functions 1 July 18th 07 02:16 PM
flipping a spreadsheet remarque Excel Worksheet Functions 2 October 27th 06 01:55 PM
Data Flipping alamsemu Excel Discussion (Misc queries) 1 March 10th 05 06:56 AM
Flipping the order around? thrava Excel Discussion (Misc queries) 4 January 14th 05 02:23 PM


All times are GMT +1. The time now is 10:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.