|
Replies: 9
| visibility 346
|
Orange Blooded [4265]
TigerPulse: 100%
Posts: 2662
Joined: 9/24/11
|
Any Microsoft Excel gurus out there ?? Need a little help.
Jan 19, 2014, 11:05 AM
|
|
Actually, the help is for Mrs Movino.
Spreadsheet issue:
Has a list of names ( a long list ) on spreadsheet.
Needs to be able to separate the LAST name into a separate column.
The names vary in characters and spaces. Some of the names are first and last....others are first, middle, last....others are first, middle1, middle2, last...and others may be first, middle initial, last.
For example:
John Smith
John David Smith
John Michael David Smith
John D Smith
The original list of names are in let's say, Column A.
She needs a Column C with LAST NAME ONLY.
She needs a Column B with the rest of the name (everything except last name).
She knows how to separate the First name only into a separate column but for the purposes of this project she does not need to separate the first name by itself.
What are we missing ? Any help would be appreciated. She has a list of 129,000 names.
Also, she is using Excel 2007, so not sure if that is part of the suxor.
Attention: Anyone of you who happens to bear the name of John Smith ( in any form mentioned above ), no worries. It's probably not you that she's after.
Thanks to anyone who may be able to help.
|
|
|
|
 |
CU Guru [1319]
TigerPulse: 100%
Posts: 1991
Joined: 10/25/06
|
|
|
|
|
|
Orange Blooded [4265]
TigerPulse: 100%
Posts: 2662
Joined: 9/24/11
|
Re: Search google for: "search excel for space from right"
Jan 19, 2014, 11:29 AM
|
|
Thank you.
That is the formula that we use if there are first and last name only.
Even when using that formula the Excel search is still done looking for the first space from the left. We can use filters to decipher thru the names with multiple spaces and characters but it has to be done manually a few items at a time.
Was hoping that Excel had a way to search from right to find first space from right and separate characters to the right of the space. That would give us last name only, despite the number of first and middle names. So far, nothing we can find will do that
|
|
|
|
|
|
Oculus Spirit [81897]
TigerPulse: 100%
Posts: 47116
Joined: 3/18/07
|
You might be able to write vba for it, but there's no
Jan 19, 2014, 12:12 PM
|
|
simple way to do it without multiple steps.
|
|
|
|
|
|
Orange Blooded [4265]
TigerPulse: 100%
Posts: 2662
Joined: 9/24/11
|
Re: You might be able to write vba for it, but there's no
Jan 19, 2014, 1:11 PM
|
|
Yep, that is what we are finding also.
|
|
|
|
|
|
Orange Blooded [3148]
TigerPulse: 100%
Posts: 3620
Joined: 1/15/09
|
This is a PITA
Jan 19, 2014, 11:57 AM
|
|
I had to do it for a fundraising mailer once.
I just did text-to-columns based on spaces, then sorted columns alphabetically to weed out the ones with blanks
|
|
|
|
|
|
All-In [33418]
TigerPulse: 100%
Posts: 32207
Joined: 2/20/04
|
It sounds like the solution is
Jan 19, 2014, 12:16 PM
|
|
to not get involved with fundraisers
|
|
|
|
|
|
Orange Blooded [4265]
TigerPulse: 100%
Posts: 2662
Joined: 9/24/11
|
PITA is accurate***
Jan 19, 2014, 1:11 PM
[ in reply to This is a PITA ] |
|
.
|
|
|
|
|
|
110%er [6272]
TigerPulse: 100%
Posts: 7290
Joined: 9/21/03
|
if memory serves me, there is a formula that will
Jan 19, 2014, 1:40 PM
|
|
that will "trim" letters to the right of a marker in a text string.
so you need to nest two formulas.
the inside formula will be to search for the first blank from the right.
then trim the letters using that value from the 1st formula.
if she can grab the first name, she should be able to interpret what i am trying to describe.
but this is off of memory and mine is going bad.
|
|
|
|
|
|
Heisman Winner [135917]
TigerPulse: 100%
Posts: 41645
Joined: 8/26/07
|
Copy column into notepad then paste back into excell
Jan 19, 2014, 3:56 PM
|
|
Pick the option that lets you use delimiters and use the space
I do it on an almost weekly basis because my coworkers can never follow format conventions
|
|
|
|
|
|
Replies: 9
| visibility 346
|
|
|
50
