Hello,
I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.
I've successfully parsed the last name in a query with the following
statement:
Last: Left([Name],InStr([Name],",")-1) which produced the following:
Smith
Jones
Blackman-Pearson
Wright
So far so good.
I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:
First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:
Ellen P.
Mary Jane
Betsy D.
George
As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:
Ellen
Mary
Betsy
George
Any help is greatly appreciated.
Thanks
WPW 4 1787
use right(first,1) = "." to identify those with initials
and then use another inStr() function to get just the first name
william wrote:
Hello,
I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.
I've successfully parsed the last name in a query with the following
statement:
Last: Left([Name],InStr([Name],",")-1) which produced the following:
Smith
Jones
Blackman-Pearson
Wright
So far so good.
I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:
First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:
Ellen P.
Mary Jane
Betsy D.
George
As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:
Ellen
Mary
Betsy
George
Any help is greatly appreciated.
Thanks
WPW
Thank you. That definitely identifies those with initials, but what
about those with the first and middle name written out (e.g. Mary
Jane)?
William
On Nov 4, 9:42 am, lesperan...@natpro.com wrote:
use right(first,1) = "." to identify those with initials
and then use another inStr() function to get just the first name
william wrote:
Hello,
I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.
I've successfully parsed the last name in a query with the following
statement:
Last: Left([Name],InStr([Name],",")-1) which produced the following:
Smith
Jones
Blackman-Pearson
Wright
So far so good.
I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:
First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:
Ellen P.
Mary Jane
Betsy D.
George
As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:
Ellen
Mary
Betsy
George
Any help is greatly appreciated.
Thanks
WPW
On 4 Nov 2006 05:34:40 -0800, william wrote:
Hello,
I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.
I've successfully parsed the last name in a query with the following
statement:
Last: Left([Name],InStr([Name],",")-1) which produced the following:
Smith
Jones
Blackman-Pearson
Wright
So far so good.
I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:
First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:
Ellen P.
Mary Jane
Betsy D.
George
As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:
Ellen
Mary
Betsy
George
Any help is greatly appreciated.
Thanks
WPW
Two things for you to consider.
1) Your first sentence formatted as Last, First, MI. < indicates
commas separating each of the names, but your examples
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
show commas only after the Last Name, and a space separating the First
and middle names/initials.
Which is it, a comma or a space?
Paste the below code into a Module:
Public Function ParseMixed(TextIn As String, SplitChar As String, x)
As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, SplitChar, -1)
ParseMixed = Var(x)
End Function
==================
In a query to separate the LastName:
LastName:ParseMixed([CombinedNames],",",0)
Then if the first names or initials are separated by a comma, i.e.
Smith, Ellen, P in a query:
FirstName:Exp: ParseMixed([CombinedNames],",",1)
However if the FirstNames are separated from each other by a space,
i.e. Smith, Ellen P. then use:
FirstName:Exp: ParseMixed([CombinedNames]," ",1)
2) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:
109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Thanks Fred and sorry about the confusion. The latter was correct. A
comma appears only after the Last Name, and a space separates the First
and middle names/initials.
Your solution worked perfectly.
Thank you for your help.
William
fredg wrote:
On 4 Nov 2006 05:34:40 -0800, william wrote:
Hello,
I've imported an excel spreadsheet with a Name column which is
formatted as Last, First, MI. Some examples I have in the Name column:
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
I need to parse it out so I have two columns, LastName and FirstName
with No Middle initial and No Middle Name. It's ok for hyphenated last
names to remain so.
I've successfully parsed the last name in a query with the following
statement:
Last: Left([Name],InStr([Name],",")-1) which produced the following:
Smith
Jones
Blackman-Pearson
Wright
So far so good.
I've parsed out everything following the comma to begin my attempt at
just grabbing the first name. I've used the following code:
First: Trim(Mid([Name],InStr([Name],",")+1)) which produced the
following:
Ellen P.
Mary Jane
Betsy D.
George
As you can see, it only works when there is one first name with no
initial. I'm stuck on how to parse out just the first name with
nothing else. This is what I evenutally want to see:
Ellen
Mary
Betsy
George
Any help is greatly appreciated.
Thanks
WPW
Two things for you to consider.
1) Your first sentence formatted as Last, First, MI. < indicates
commas separating each of the names, but your examples
Smith, Ellen P.
Jones, Mary Jane
Blackman-Pearson, Betsy D.
Wright, George
show commas only after the Last Name, and a space separating the First
and middle names/initials.
Which is it, a comma or a space?
Paste the below code into a Module:
Public Function ParseMixed(TextIn As String, SplitChar As String, x)
As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, SplitChar, -1)
ParseMixed = Var(x)
End Function
==================
In a query to separate the LastName:
LastName:ParseMixed([CombinedNames],",",0)
Then if the first names or initials are separated by a comma, i.e.
Smith, Ellen, P in a query:
FirstName:Exp: ParseMixed([CombinedNames],",",1)
However if the FirstNames are separated from each other by a space,
i.e. Smith, Ellen P. then use:
FirstName:Exp: ParseMixed([CombinedNames]," ",1)
2) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:
109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rutger Claes |
last post by:
I'm trying to make sort of a simple template system using XML.
Imagine the following xml code:
<page>
<header>...</header>
<body>
<title>My News</title>
<?news to_list 10?>
</body>
</page>
|
by: Michael Hogan |
last post by:
I want to pars a playlist file for three different varibles, so I can save
them as mp3 files. I am using:
strTEMPURL = GetUrlSource(Text1.Text)
to put the entire .pls file into a strTEMPURL...
|
by: Joey Martin |
last post by:
Couple questions when parsing using replace.
I have the following text I am parsing:
$650
Number of Bedrooms 3
Air Conditioning? Yes
Original Ad SOUTH, 3BR, air, basement. $650. Call 278-4171....
|
by: Vegard Beider |
last post by:
Hi.
I am new to both C# and XML parsing. I simply want to find out if a
node contains other nodes, and if it does i want to do something with
it. How can i find out in a simple way that in the...
|
by: Tuomas Rannikko |
last post by:
Hello,
I'm currently writing a XML processor for the fun of it. There is
something I don't understand in the spec though. I'm obviously missing
something important.
The spec states that both...
|
by: sbowman |
last post by:
I have a completely lame string parsing question, but I need an answer
fast and I know this is where to get it...I'm not completely familiar
with the Len, Right, Left, and mid functions and I have...
|
by: igotyourdotnet |
last post by:
I have a question.
I'm reading a CSV file that is uploading to my SQL db, I'm parsing out the
file line by line. I'm getting the values and putting them into an arrayList
seperate by commas. The...
|
by: Bryan |
last post by:
If I have the following string from a huge xml file:
std::string s = "<input key1=\"v1\" key2=\"val4\" key3=\"test\" />";
I need to get the values associated with the keys out from this line....
|
by: cjl |
last post by:
As a learning exercise, I am trying to write a web-based version of
'drawbot' in PHP.
See: http://just.letterror.com/ltrwiki/DrawBot
I am interested in hearing ideas about how to approach...
|
by: i_robot73 |
last post by:
I have a file, containing hex values for dates (MMDDYYYY)<status
code><??such as:
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |