palaeontology
Active Member
- Joined
- May 12, 2017
- Messages
- 444
- Office Version
- 2016
- Platform
- Windows
In cell OH6 I have a concatenation of cell contents from two ranges ... EH6 to GR6 as well as LV6 to OF6 .... each of those cells might or might not have a 4-digit number within it.
That's 126 different cells being concatenated into one cell with a single space placed between each.
So as it currently sits, the concatenated cell (OH6) has about thirteen 4-digit numbers separated by a single space each, then about 50 single spaces (those are the 13 numbers and 50 spaces that were concatenated from the first of the two ranges ... EH6 to GR6), before the umbers from the 2nd range begin to appear and the spaces that followed those .... see image below ...
In the range OJ6 to TE6 I'm trying to un-concatenate (or split) the contents of OH6.
I'm currently using the following formula ... =MID($OH6,FIND(CHAR(160),SUBSTITUTE($OH6," ",CHAR(160),COLUMN()-400))+1,4) ... which works perfectly until it reaches the first location within the concatenated OH6 where there is no 4-digit number, so the 2nd group of numbers (the ones you can see in the right-hand side of the image) don't get recognised
Is there an easier way for me to split or un-concatenate the contents of Cell OH6 ????
I'd prefer to use a formula (if it can be done) and not rely on me manually splitting the cell, as the spreadsheet needs to be a stand-alone that other teachers (not usually literate in Excel) can use the end-product of.
I fear I have not explained the problem well,
Please let me know if there is any other information I would need to convey .
Kind regards,
Chris
That's 126 different cells being concatenated into one cell with a single space placed between each.
So as it currently sits, the concatenated cell (OH6) has about thirteen 4-digit numbers separated by a single space each, then about 50 single spaces (those are the 13 numbers and 50 spaces that were concatenated from the first of the two ranges ... EH6 to GR6), before the umbers from the 2nd range begin to appear and the spaces that followed those .... see image below ...
In the range OJ6 to TE6 I'm trying to un-concatenate (or split) the contents of OH6.
I'm currently using the following formula ... =MID($OH6,FIND(CHAR(160),SUBSTITUTE($OH6," ",CHAR(160),COLUMN()-400))+1,4) ... which works perfectly until it reaches the first location within the concatenated OH6 where there is no 4-digit number, so the 2nd group of numbers (the ones you can see in the right-hand side of the image) don't get recognised
Is there an easier way for me to split or un-concatenate the contents of Cell OH6 ????
I'd prefer to use a formula (if it can be done) and not rely on me manually splitting the cell, as the spreadsheet needs to be a stand-alone that other teachers (not usually literate in Excel) can use the end-product of.
I fear I have not explained the problem well,
Please let me know if there is any other information I would need to convey .
Kind regards,
Chris
