Split column contents (de-concantenate)

Discussion in 'Microsoft Office' started by Andersn, Sep 8, 2018.

  1. Andersn

    Andersn

    Joined:
    Sep 8, 2018
    Likes Received:
    4
    I have a difficult Excel problem and would like some help.
    I have an Excel column that contains text data as follows:

    1. text containing words and spaces.
    2. text containing words and spaces.
    3. text containing words and spaces.
    4. text containing words and spaces.

    I would like to split this text into 4 separate columns. The text in each is of different lengths and contains different text.

    One column containing all the "1. text containing words and spaces."
    Another column containing all the "2. text containing words and spaces."
    Another column containing all the "3. text containing words and spaces."
    Another column containing all the "4. text containing words and spaces."

    Can that be done?

    Thank you, Jeff
     
    Andersn, Sep 8, 2018
    #1
    1. Advertisements

  2. Andersn

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,397
    Location:
    Manchester
    Welcome to the forum :)

    What are the parameters for putting the text from the start column to the end column - is it the number it begins with? So all data beginning with "1." goes into the first column, all data beginning with "2." goes into the second column, etc?
     
    Becky, Sep 9, 2018
    #2
    1. Advertisements

  3. Andersn

    Andersn

    Joined:
    Sep 8, 2018
    Likes Received:
    4
    Thanks for replying Becky.

    Yes, that is correct. It is the number they begin with. Those starting with "1." go into a column, 2. goes into another etc.
     
    Andersn, Sep 9, 2018
    #3
  4. Andersn

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,397
    Location:
    Manchester
    Ok thanks. I'm not sure where the de-concatenate comes in though, did you want to split the contents of the cells? Is each example of "1. text containing words and spaces" in a different cell or the same cell?
     
    Becky, Sep 9, 2018
    #4
  5. Andersn

    Andersn

    Joined:
    Sep 8, 2018
    Likes Received:
    4
    That is correct. The excel sheet currently contains 2 columns. One column is a list of surnames.
    The second column is the one I want to deconcanate. It contains over 1000 cells of text. Each cell contains text like this:

    1. text containing words and spaces.

    2. text containing words and spaces.
    3. text containing words and spaces.


    4. text containing words and spaces.​

    - The is an inconsistent number of blank lines between the numbered lines but the numbered lines are in numerical order,
    - There is only one line of text in each cell that would start with "1." or "2.", etc. So the number (followed by a period) is the separater. But the text after each number may contain one or more sentences. So the period "." cannot be the separator. Also some of the text may contain dates. So numbers need to be followed by a period to be a ligitimate separator.
    - but not all cells contain the full complement 1, 2, 3, etc. Some cells may not contain one or two such numbered lines. That is they may (for example) contain only 1, 3, and 4 (missing 2.) and similar.

    I attached an example.

    Is it feasible?
     

    Attached Files:

    Andersn, Sep 9, 2018
    #5
    Becky likes this.
  6. Andersn

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,397
    Location:
    Manchester
    Great, thank you for the spreadsheet.

    This is what I've come up with:

    =IF(AND(IFERROR(LEFT(B3)*1,FALSE),ISNUMBER(SEARCH("*.",B3))),B3,"")

    This basically asks if both the first character is a number, and if the cell contains a number followed by a full stop, then it will reproduce the contents of the cell. If it doesn't contain those two things, the cell will be blank. You can copy this down the entire column.

    The problem is that you will have blank cells... You could get rid of them by using a filter when you need to see just that list, or alternatively it may be possible to use a VLOOKUP in the fomula to get a blank-free list.

    Hope this helps!
     
    Becky, Sep 10, 2018
    #6
  7. Andersn

    Andersn

    Joined:
    Sep 8, 2018
    Likes Received:
    4
    Thank you so much Becky. I did not think this was solvable and was not looking forward to do it all manually. I really appreciate it. It's part of an archival medieval research project I am working on.
     
    Andersn, Sep 11, 2018
    #7
    Becky likes this.
  8. Andersn

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,397
    Location:
    Manchester
    Glad to hear it helped! :) Sounds like an interesting project, hope it goes well. Let me know if there's anything else I can help with.
     
    Becky, Sep 11, 2018
    #8
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.