+ Reply to Thread
Results 1 to 10 of 10

String of Values

  1. #1
    Registered User
    Join Date
    09-08-2015
    Location
    usa
    MS-Off Ver
    2010
    Posts
    73

    String of Values

    I have a string of values in one cell that I want to break down into 2 columns, here is the format of the values within the cell;

    28 Waist 28 Inseam,28 Waist 29 Inseam,28 Waist 30 Inseam,28 Waist 31 Inseam,28 Waist 32 Inseam,28 Waist 33 Inseam,28 Waist 34 Inseam,28 Waist 35 Inseam,28 Waist 36 Inseam,28 Waist 37 Inseam,28 Waist 38 Inseam,29 Waist 28 Inseam

    It goes on like this for a good while, I am sure there is something I can run to break it up into columns correct? I need the first column to be the # waist and the second column # inseam.

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: String of Values

    If they are all same format then use following.
    If entries are in column A then column B and C will have:

    HTML Code: 
    and
    HTML Code: 
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: String of Values

    Is all of that text in the one cell? Because you say you want it extracted into 2 columns. How would that look for the data provided?

  4. #4
    Registered User
    Join Date
    09-08-2015
    Location
    usa
    MS-Off Ver
    2010
    Posts
    73

    Re: String of Values

    That only will pull one value, I was wondering if there is something I could run to give something like this

    column a column b

    28 waist 28 inseam
    28 waist 29 inseam
    28 waist 30 inseam
    28 waist 31 inseam
    etc etc
    etc etc

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

    Re: String of Values

    I am assuming you want the commas to represent a new row, and then separate each into 2 columns. Here's how I would probably do it in Excel:

    1) Text to columns using comma as delimiter.
    2) Transpose result of 1.
    3) Text to columns using space as delimiter, which gives me 4 columns # -- waist -- # -- inseam. In most of what I do, this would be superior to two columns, because the numbers are separate from the text, making it easier to access the numbers (since I usually want to do something with the numbers). If you really want only 2 columns, then use Fixed width with the break between "Waist" and the following number.

    If I were doing this in Google Sheets, I could use Google's SPLIT() function to perform the same sequence in a formula rather than a menu command.

    If I were familiar with Power Query (Get and Transform in 2016), I expect that this could be accomplished using that feature's M language (and might be the best approach).

    Questions?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    09-08-2015
    Location
    usa
    MS-Off Ver
    2010
    Posts
    73

    Re: String of Values

    Yes all that text is in one cell and I want to break it down into column a and column b

  7. #7
    Registered User
    Join Date
    09-08-2015
    Location
    usa
    MS-Off Ver
    2010
    Posts
    73

    Re: String of Values

    I was able to get each size "28 waist 28 inseam" etc in their own cells, I now have 40 or so columns of sizes and I want to get them into the 2 or 4 columns with the waist and inseam in separate columns. I am not familiar with the transpose function, I tried playing with it but no luck. Any help?

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

    Re: String of Values

    Are you talking about using the TRANSPOSE() worksheet function (an array function, probably the easiest mistake to make with this function is to neglect the ctrl-shift-enter step when entering the function), or something else?

    I would probably just select the resulting 40 or so columns of "xx waist yy inseam" cells, copy to clipboard (ctrl-c), select a destination cell, paste special -> transpose.

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: String of Values

    Will this work for you?
    Enter formula in B2 and drag it across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C
    1 28 Waist 28 Inseam,28 Waist 29 Inseam,28 Waist 30 Inseam,28 Waist 31 Inseam,28 Waist 32 Inseam,28 Waist 33 Inseam,28 Waist 34 Inseam,28 Waist 35 Inseam,28 Waist 36 Inseam,28 Waist 37 Inseam,28 Waist 38 Inseam,29 Waist 28 Inseam
    2 28 Waist 28 Inseam
    3 28 Waist 29 Inseam
    4 28 Waist 30 Inseam
    5 28 Waist 31 Inseam
    6 28 Waist 32 Inseam
    7 28 Waist 33 Inseam
    8 28 Waist 34 Inseam
    9 28 Waist 35 Inseam
    10 28 Waist 36 Inseam
    11 28 Waist 37 Inseam
    12 28 Waist 38 Inseam
    13 29 Waist 28 Inseam
    14
    Attached Files Attached Files
    Last edited by AlKey; 07-03-2018 at 02:43 PM. Reason: Added spreadsheet
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  10. #10
    Registered User
    Join Date
    07-12-2018
    Location
    Greater New Orleans, Louisiana, USA
    MS-Off Ver
    2010
    Posts
    23

    Re: String of Values

    Look into the SPLIT function. Since this is an Access sub-forum, that should be available to you. You would use the SPLIT to separate the comma-separated elements in step one. Then you can use LEFT$ and RIGHT$ functions to split apart the two sub-sections of the resulting string. But you show your data in an Excel format under a program heading of Access. Which one is actually in use?

+ 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. Discern deafult values from assigned values of Integer, Long, Double, String
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2016, 07:16 PM
  2. Script to Search for a specific String then return cell values above that String-4
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2015, 01:28 AM
  3. [SOLVED] Script to Search for a specific String then return cell values above that String-3
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 01:50 PM
  4. [SOLVED] Script to Search for a specific String then return cell values above that String-2
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 01:00 PM
  5. [SOLVED] Script to Search for a specific String then return cell values above that String
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 11:28 AM
  6. Replies: 2
    Last Post: 12-29-2014, 11:24 PM
  7. Replies: 5
    Last Post: 11-08-2012, 03:38 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