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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,302
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
52,302
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,123
Messages
5,768,246
Members
425,460
Latest member
Astros1243

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