By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Separating One Field with Commas into 2 Separate Fields

P: 6
Hi All (Access 2003/XP)

I have an Excel file with name information in the form of LastName, First Name in one field. I want to have the name separated into 2 fields, LastName and First Name for Access. I have googled this problem and found th following solution from this forum:

On 25 Feb 2005 16:05:15 -0800, michellep@stupid.com wrote:
[color=blue]
> I have an excel spreadsheet that I imported into Access, and there is a
> Name field in the format of last name, first name. I'd like to break
> this into 2 separate fields. How can I do this?
> TIA![/color]

As long as all of the data is in the form of
LastName, FirstName then:

[LastName] = Left([FullName],InStr([FullName],",")-1)
[FirstName] = Mid([FullName],InStr([FullName],",")+2)
--
Is this the right way to do this? If Yes, where would this code be entered?
Jul 23 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
Hi All (Access 2003/XP)

I have an Excel file with name information in the form of LastName, First Name in one field. I want to have the name separated into 2 fields, LastName and First Name for Access. I have googled this problem and found th following solution from this forum:



Is this the right way to do this? If Yes, where would this code be entered?
Yes, the code is correct and would be entered into 2 Calculated Fields named LastName and FirstName. I assume that you are Importing the Excel data. In that case create a new Query based on the Table containing the Imported Data, then Copy and Paste this SQL into the SQL View Window. Next, run the Query. Hope this helps.
Expand|Select|Wrap|Line Numbers
  1. SELECT FullName, Left([FullName],InStr([FullName],",")-1) AS LastName, Mid([FullName],InStr([FullName],",")+2) AS FirstName
  2. FROM tblNames;
Jul 23 '07 #2

Expert 100+
P: 296
Another solution is to separate the fields in Excel first. If you go to Data then Text to Columns and select Delimited, and use a comma as your delimiter, it will split the cell up into 2 columns for you.
Jul 23 '07 #3

P: 6
i will try these 2 suggestions out..thanks for the help
Jul 23 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.