MS Access Parsing or Splitting a text field in a table

jetera used Ask the Experts™
Hi Everyone,

Can someone show me how to parse this:
Val      VV      Values
7            0 - no
7            1 - yes
6            M - male
6            F - female

into this in my Table1:

Val      VV      Values
7      0      no
7      1      yes
6      M      male
6      F      female

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Hmmm my columns did not line up well when I submitted this.  

Val      VV      Values
7                      0 - no
7                      1 - yes
6                      M - male
6                      F - female

to this:
Val      VV      Values
7         0          no
7         1          yes
6         M          male
6         F          female
I may suggest to use Excel in separating the text, then import into Access once done.



Sure- but I don't know how to do that either- do you?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Here are the steps when you separate text in Excel:

1. Copy the table into Excel
2. Select the Values Column
3. Data Tab -> Data Tools -> Text to Columns
4. Follow the Wizard and type "-" in Other box of the Step 2-3 wizard.
5. Once they are separated, assigned the correct column labels as  Val, VV and Values

Once completed, Open your access database:
1. External Data -> Import -> Excel
2. Follow the wizard

Or if you have already Table 1, just copy the data from Excel and paste it directly into Table1.

It's a long process but this is what I think easiest for me to achieve.

Expert of the Year 2008
Top Expert 2008

SELECT [Val], Left(Values,1) as [VV], Mid([Values],3) as [Vals] FROM [TableName]



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial