Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Parsing text in access 2007

Posted on 2007-10-21
Medium Priority
Last Modified: 2008-05-19
I have imported some datas to access 2007 from a text file. One of the field is an artist full name with 2 or 3 words included (Elton John) or (Evelyn Peggy March).
How can I parse this "full name" field to 2 separated fields (First Name and Last Name) like (John, Elton) or (March, Evelyn Peggy) ?
Please note that I'm a novice and I cannot maybe understand when you explain only the function (without how and where I must enter).
Question by:cisilay
  • 2
  • 2
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20118691
Hello cisilay,

This is actually a more complex question than I think you realize.  Have a look at this
current question for a sense of the difficulties behind what, at first glance, sounds
like such a simple thing:




Author Comment

ID: 20119094
These kind of formulas are very complicated for me.
OK, if I want only parse 2 words from one "full name" field to 2 separated fields (First Name) (Last Name), without suffix or without middle name, like only from (Elton John) to (Elton) and (John), is it easy  and if yes how ?
Please don't forget to answer for a novice user.
LVL 93

Accepted Solution

Patrick Matthews earned 900 total points
ID: 20119413

If all of the following are true:

1) There is *always* a fname and *always* a lname
2) Both fname and lname are always one word each
3) The fname is always listed first
4) The two are always separated by a space

then the following query would separate them:

SELECT Left(FullName, InStr(1, FullName, " ") - 1) AS FName, Mid(FullName, InStr(1, FullName, " ") + 1) AS LName
FROM YourTable



Author Comment

ID: 20120626
OK thanks, it works well.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question