I have over a thousand cells with last name,first name. I want two different
cells with last name in one and first name in the other (minus the comma).
Any ideas? (no VB please!)
Thanks.
--
Message posted via http://www.officekb.com
I have over a thousand cells with last name,first name. I want two different
cells with last name in one and first name in the other (minus the comma).
Any ideas? (no VB please!)
Thanks.
--
Message posted via http://www.officekb.com
Actually, I don't have a comma. It's just LastName FirstName MiddleInitial
all separated by spaces
Patty wrote:
>I have over a thousand cells with last name,first name. I want two different
>cells with last name in one and first name in the other (minus the comma).
>Any ideas? (no VB please!)
>
>Thanks.
--
Message posted via http://www.officekb.com
Hi, Patty. See Data-->Text to Columns about 1/3rd down the page here:
http://www.officearticles.com/tutori...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com
"Patty via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> Actually, I don't have a comma. It's just LastName FirstName MiddleInitial
> all separated by spaces
>
>
> Patty wrote:
>>I have over a thousand cells with last name,first name. I want two
>>different
>>cells with last name in one and first name in the other (minus the comma).
>>Any ideas? (no VB please!)
>>
>>Thanks.
>
>
> --
> Message posted via http://www.officekb.com
Try this:
Assuming the name is in cell A1:
B1 =LEFT(A1,FIND(",",A1,1)-1)
C1 =RIGHT(A1,LEN(A1)-FIND(",",A1,1)-1)
"Patty via OfficeKB.com" wrote:
> I have over a thousand cells with last name,first name. I want two different
> cells with last name in one and first name in the other (minus the comma).
> Any ideas? (no VB please!)
>
> Thanks.
>
>
> --
> Message posted via http://www.officekb.com
>
This relpy is for Lastname, Firstname.
"David Hepner" wrote:
> Try this:
>
> Assuming the name is in cell A1:
>
> B1 =LEFT(A1,FIND(",",A1,1)-1)
> C1 =RIGHT(A1,LEN(A1)-FIND(",",A1,1)-1)
>
>
> "Patty via OfficeKB.com" wrote:
>
> > I have over a thousand cells with last name,first name. I want two different
> > cells with last name in one and first name in the other (minus the comma).
> > Any ideas? (no VB please!)
> >
> > Thanks.
> >
> >
> > --
> > Message posted via http://www.officekb.com
> >
Dump it into Word. Convert table to text. Convert text to table and use a
space as the separator then dump to excel
"Patty via OfficeKB.com" wrote:
> I have over a thousand cells with last name,first name. I want two different
> cells with last name in one and first name in the other (minus the comma).
> Any ideas? (no VB please!)
>
> Thanks.
>
>
> --
> Message posted via http://www.officekb.com
>
How about just selecting the column
data|text to columns
delimited
by space
(leave enough room to the right to accept all your fields)
"Patty via OfficeKB.com" wrote:
>
> I have over a thousand cells with last name,first name. I want two different
> cells with last name in one and first name in the other (minus the comma).
> Any ideas? (no VB please!)
>
> Thanks.
>
> --
> Message posted via http://www.officekb.com
--
Dave Peterson
Text-to-columns will fill the bill as long as ALL cells include three parts.
If, in some, the middle initial is missing, for those rows, you'll end up with
the last name in the column intended for the middle initial.
If this is a problem, you can perhaps solve it quickly by sorting on the 3rd
column (should be the last name). Those with no last name will go to the
bottom of the list. For those rows, you can then insert cells between the
first and last names, to provide for the blank middle initial
On Mon, 12 Sep 2005 11:36:23 -0400, "Anne Troy" <[email protected]> wrote:
>Hi, Patty. See Data-->Text to Columns about 1/3rd down the page here:
>http://www.officearticles.com/tutori...soft_excel.htm
>************
>Anne Troy
>www.OfficeArticles.com
>
>
>
>"Patty via OfficeKB.com" <[email protected]> wrote in message
>news:[email protected]...
>> Actually, I don't have a comma. It's just LastName FirstName MiddleInitial
>> all separated by spaces
>>
>>
>> Patty wrote:
>>>I have over a thousand cells with last name,first name. I want two
>>>different
>>>cells with last name in one and first name in the other (minus the comma).
>>>Any ideas? (no VB please!)
>>>
>>>Thanks.
>>
>>
>> --
>> Message posted via http://www.officekb.com
>
Oops... I missed the part of your original message where you describe the data
order. My first reply was based on the erroneous assumption that it was First
MI Last (which is a more common situation when all data is in one cell with no
comma delimiter).
Since the order is Last First MI, a row without a middle initial is not a
problem.
On Mon, 12 Sep 2005 13:26:59 -0500, Myrna Larson
<[email protected]> wrote:
>Text-to-columns will fill the bill as long as ALL cells include three parts.
>If, in some, the middle initial is missing, for those rows, you'll end up
with
>the last name in the column intended for the middle initial.
>
>If this is a problem, you can perhaps solve it quickly by sorting on the 3rd
>column (should be the last name). Those with no last name will go to the
>bottom of the list. For those rows, you can then insert cells between the
>first and last names, to provide for the blank middle initial
>
>On Mon, 12 Sep 2005 11:36:23 -0400, "Anne Troy" <[email protected]>
wrote:
>
>>Hi, Patty. See Data-->Text to Columns about 1/3rd down the page here:
>>http://www.officearticles.com/tutori...soft_excel.htm
>>************
>>Anne Troy
>>www.OfficeArticles.com
>>
>>
>>
>>"Patty via OfficeKB.com" <[email protected]> wrote in message
>>news:[email protected]...
>>> Actually, I don't have a comma. It's just LastName FirstName MiddleInitial
>>> all separated by spaces
>>>
>>>
>>> Patty wrote:
>>>>I have over a thousand cells with last name,first name. I want two
>>>>different
>>>>cells with last name in one and first name in the other (minus the comma).
>>>>Any ideas? (no VB please!)
>>>>
>>>>Thanks.
>>>
>>>
>>> --
>>> Message posted via http://www.officekb.com
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks