Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003

    Unanswered: Text to Column In Access Using VB

    We can perform this in Excel(Text to Column), when we have in one column let say the text like

    Mary Emrick
    John Kovac

    We want it to be like:-

    Column1 Column2
    Mary Emrick
    John Kovac

    It use space as delimiter to separate it into different column


    What about in Access, I have a table with one field:-

    Mary Emrick
    John Kovac

    USING VB, I want it to be :-

    Field1 Field2
    Mary Emrick
    John Kovac

    I mean I want to separate it by referring to the space delimiter
    in the data.


  2. #2
    Join Date
    May 2002
    Use the function Instr to find the position of the space and then you can use left, right, mid to split out the first and last names.

    I'll do the SQL later if you need but I've havn't got time right now.

  3. #3
    Join Date
    Jul 2003

    Re: Text to Column In Access Using VB

    Setup a query using your table, this will give you your firstname:
    FirstName: Left([PersonName],InStr([PersonName]," ")-1)

    This will give you your second name:
    SecondName: Mid([PersonName],InStr([PersonName]," ")+1,Len([PersonName])-InStr([PersonName]," "))

  4. #4
    Join Date
    Oct 2002
    Leicester - UK
    the command you need are
    InStr(<startingchar>, <StringToSearch>, <StringToFind>)

    you'lll then need to add the data to a new table which can be done in many ways i'd suggest using docmd.runsql(<string>) to reform an append query

    that should get you started i'll throw up some examples if you need more help
    Last edited by m.timoney; 07-23-03 at 05:58.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Nov 2014

    How to Split Text into Different Columns?

    A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.

    Following are the steps to split data into different columns using ‘Text to columns’ :

    1 Select the range of data you need to work at.
    2 Select Data from menu and select Text to Columns in the Data Tools group.
    3 Check Delimited in the dialog box (1 of 3) and click Next.
    In Step 2 of 3, check the box related to the specific separator in the information.
    If the separator is tab, check tab or if the separator
    is semicolon, check semicolon and so on.
    If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:
    4 Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.
    5 Enter the Destination (C3) to paste the data after splitting and Click Finish.
    6 Click Ok in the dialogue box.

    How to Split Text into Different Columns.pdf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts