Split column contents (de-concantenate)

[Andersn]

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

 

[Becky]

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?

 

[Andersn]

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.

 

[Becky]

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?

 

[Andersn]

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?

 

[Becky]

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!

 

[Andersn]

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.

 

[Becky]

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.