+ Reply to Thread
Results 1 to 5 of 5

Very specific text to columns

  1. #1
    Registered User
    Join Date
    11-22-2016
    Location
    New York, NY
    MS-Off Ver
    2016
    Posts
    37

    Very specific text to columns

    Hi all,

    I have a spreadsheet with a column that contains "category codes" along with some other factors. There is always at least one category code and so far the maximum I have seen is 7.

    How can I separate the column in access to show me all of the category codes in different columns? Below are some examples of how the original column comes through:

    PI97*72172.86*10097*3
    OA133*54.5*12133*5,MA67**6067*
    CO131*2419.92*9131*2,CO137*-193.76*9137*2,PR2*1102.92*8002*1

    All of the category codes except for the first one appear to be sandwiched between a comma(,) and an asterisk(*). I would want this column to be split as follows:

    Code1 Code2 Code3 ...Code7
    PI97
    OA133 MA67
    CO131 CO137 PR2

    Any help would be much appreciated, thanks!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Very specific text to columns

    I would probably use regular text to columns to split the data at the comma. Then use a LEFT(...,FIND(...)) combination to extract the category code from the start of each resulting text string.

    Text to columns help: https://support.office.com/en-us/art...A-7A3E9C363ED7
    Excel functions help: https://support.office.com/en-us/art...c6d90033e18847

    Is there a part of putting that together that you have difficulty with?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    11-22-2016
    Location
    New York, NY
    MS-Off Ver
    2016
    Posts
    37

    Re: Very specific text to columns

    I should have specified that I'd prefer to do this in Access and not Excel, so I was wondering if there is a way to duplicate the Excel text to columns method in Access.

    I believe I could create a macro in Excel to do this but then I would have to change the Access database that is linked to the Excel spreadsheet.

    Sorry, should have been more clear.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Very specific text to columns

    My mistake, I should have seen that this was for access.

    A quick internet search for "text to columns in access" finds many references that suggest using a Split() function. It wasn't clear to me if this was the VBA Split() function (https://msdn.microsoft.com/VBA/Langu...split-function ) or if Access has its own split() function. Some of the examples specified applying this function to the query definition and did not seem to apply to prepping the data for storage in the database. Perhaps the expected approach would be store the data as is in a database, then use these queries to extract the desired category codes (with associated data or not?) from that database to store in a new database.

  5. #5
    Registered User
    Join Date
    11-22-2016
    Location
    New York, NY
    MS-Off Ver
    2016
    Posts
    37

    Re: Very specific text to columns

    OK thanks, I will look more into the Split() function and update this thread if I find a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] copy row with text in specific colum inbeween othre rows with text in other columns
    By Louisa Venter in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-14-2015, 12:07 AM
  2. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  3. [SOLVED] fill down specific columns with specific text
    By Excelsemi in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-03-2014, 08:04 AM
  4. Programming to automatically send text to specific sheets and specific columns.
    By GlobalExcelller in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-21-2014, 10:07 PM
  5. Delete 1 to 8 rows. Do text to columns to specific columns.
    By niceblue in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2013, 10:53 AM
  6. Add Columns with specific text in a row
    By TheTank67 in forum Excel General
    Replies: 4
    Last Post: 03-22-2013, 01:56 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1