# UNCONCATENATE CONCATENATED TEXT

#### palaeontology

##### Active Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Peter_SSs

##### MrExcel MVP, Moderator
If they are all 4-digit numbers then possibly this?

21 09 04.xlsm
OHOIOJOKOLOMONOOOPOQOR
61234 9999 2225 1526 3265 4587 4587 9586 2514 123499992225152632654587458795862514
Split numbers
Cell Formulas
RangeFormula
OI6:OR6OI6=MID(TRIM(\$OH6),COLUMNS(\$OI:OI)*5-4,4)

#### palaeontology

##### Active Member
If they are all 4-digit numbers then possibly this?

21 09 04.xlsm
OHOIOJOKOLOMONOOOPOQOR
61234 9999 2225 1526 3265 4587 4587 9586 2514 123499992225152632654587458795862514
Split numbers
Cell Formulas
RangeFormula
OI6:OR6OI6=MID(TRIM(\$OH6),COLUMNS(\$OI:OI)*5-4,4)

#### palaeontology

##### Active Member
Peter, thankyou so much for that.

Works perfectly, as do all your suggestions.

Very kind regards,

Chris

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

Replies
24
Views
1K
Replies
8
Views
600
Replies
12
Views
1K
Replies
4
Views
440
Replies
6
Views
578

1,187,172
Messages
5,962,029
Members
438,575
Latest member
aleksa02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back