| 0 |
Subject: Excell function question
Posted by: rockafellerskank
- Dude [27652109] Tue, Jun 15, 2004, 22:42
I have 2 sets of data that I want to cut down to "last name" The different sets of data are formatted as follows:
Data 1:
Smith, John
Harvey, Kevin
Doe, John
Data 2:
Anderson Bob
Bryant Kobe
Jones Terry
I want cut or carve out their last name (Anderson, Smith Jones ... whatever) So, I assume I need to use some sort of LEFT command but I can't use the normal wizard because it's not the same number of characters (IE 4,5,6,7, ?). Is there a way to config a command that will give me every thing- character LEFT until it hits a space? And a comma?
Or really.... even better, break down the last names/first name into 2 cells of Lastname-FirstName
|
| 1 | rockafellerskank
Dude
ID: 27652109
Tue, Jun 15, 2004, 22:59
|
errrr. Actually, Data 2 is:
Data 2:
Bob Anderson
Kobe Bryant
Terry Jones
... so I guess I need a variation of the RIGHT function.
|
| 2 | MadDOG
ID: 475181519
Tue, Jun 15, 2004, 23:11
|
No way to do that as far as I know.
You can use the SEARCH function to find the comma. But I don't know what you could do from that point.
Somehow make everything to the right of the comma blank.
SEARCH
FIND
EXACT
LEFT
RIGHT
Those are all functions that find text.
|
| 3 | MadDOG
ID: 475181519
Tue, Jun 15, 2004, 23:23
|
If you do find a way to do this, please put it into this thread. I would like to see that. Thanks.
|
| 4 | ukula
ID: 20831621
Tue, Jun 15, 2004, 23:30
|
rfs - you don't need to use a function. For Data Set 1, highlight the column and select "Data, Text to Columns". The Wizard will pop up. Select Delimited, and then on the next screen select "comma". Click "Finish" and your last name will be in column A and your first name will be in column B.
For Data Set 2, do the same thing except instead of selecting "comma" select "space". Click "Finish" and your first name will be in column A and your last name will be in column B.
|
| 5 | MadDOG
ID: 475181519
Tue, Jun 15, 2004, 23:33
|
Hey, try this.
Use the FIND/REPLACE command on the edit menu.
Find ,_* that's comma space star
Replace leave empty
Hit replace all
it worked for me.
|
| 6 | ukula
ID: 20831621
Tue, Jun 15, 2004, 23:33
|
For Data Set 1, if you want to keep the comma at the end of the last name, use "space" delimited instead of "comma" delimited.
I use this method all the time to parse text files from various sources.
|
| 7 | MadDOG
ID: 475181519
Tue, Jun 15, 2004, 23:40
|
I used Find _* that's space star
Leave REPLACE empty and it took the second word off every name he has up there.
|
| 8 | rockafellerskank
Dude
ID: 27652109
Wed, Jun 16, 2004, 00:14
|
Thanks ukula. That worked fine.
(and Thanks MadDog for your input as well)
|
| 9 | youngroman
ID: 221118186
Wed, Jun 16, 2004, 04:18
|
when A1 is the cell with format aaa, bbb:
here are the "german" formulas (only have a german excel):
cell B1: =LINKS(A1;FINDEN(", ";A1)-1)
cell C1: =TEIL(A1;FINDEN(", ";A1)+2;100)
that should read in english:
cell B1: =LEFT(A1;FIND(", ";A1)-1)
cell C1: =MID(A1;FIND(", ";A1)+2;100)
you could do a more complicated formula to get rid of the 100 in C1 and compute the exact length:
=MID(A1;FIND(", ";A1)+2;LENGTH(A1)-FIND(", ";A1)-1)
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|