UNCONCATENATE CONCATENATED TEXT

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. 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 ...

creengrab for mr excel.JPG


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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
Peter, thankyou so much for that.

Works perfectly, as do all your suggestions.

Very kind regards,

Chris
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up.
 

Forum statistics

Threads
1,147,477
Messages
5,741,362
Members
423,657
Latest member
Medrok2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top