Split cells based on specific text which may occur multiple times in cell

charlie_580

Board Regular
Joined
Feb 3, 2007
Messages
56
Hi

I wonder if anyone can help?

I need to split multiple cells by code, however the code may occur within the original cell more than once. and example of my original cell has the following:

D11|1|2|text|numbers|text|numbers|text|numbers|text|numbers||||||||||||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numberstext|numbers|text|numbers||| |text|numbers|text|555|D11|32232|text|numbers|text|numbers||||text|numbers|text|3442 F12|4332|text|numbers|text|numbers|text|numbers|text|numbers|||||5353 D11||text|numbers|text|numbers||||| D11||text|numbers|text|numbers| |text|numbers|text|numbers|text|numbers|text|numbers|F17 text|numbers|text|numbers|text|numbers |text|numbers|text|numbers|text|numbers D11||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers| D11||text|numbers|text|numbers D11||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|D40|numbers|text|numbers D11|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers

I need the text extracted from the first code up to the second code and so on. My first cell of extracted data would be
D11|1|2|text|numbers|text|numbers|text|numbers|text|numbers||||||||||||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numberstext|numbers|text|numbers||| |text|numbers|text|555|

The second would be
D11|32232|text|numbers|text|numbers||||text|numbers|text|3442

and the third
F12|4332|text|numbers|text|numbers|text|numbers|text|numbers|||||5353
and so on.

Once extracted, I would like to then insert the number of rows required for each extracted cell and list them all in the one column.
If I don't make sense, or if anyone can help, let me know.
Thanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Yes.

F12
F17
F18
F19
F20
D04
D11
D20

There are others but they are numerical so I thought they probably can't be included in a formula?

Cheers
 
Upvote 0
There are others but they are numerical so I thought they probably can't be included in a formula?
Why don't you tell us what they are anyway just in case we can see something in them that will allow us to identify them from among your other numbers.
 
Upvote 0
Will they always be preceded by a space or vertical bar symbol and always followed by a vertical bar symbol (like your other codes appear to be?

Also, if the answer to the above is yes, can we assume that if 51, 63 or 65 appears that way then it is always going to be a code number?
 
Upvote 0
Yes. Mostly preceeded by a bar and a space and followed by a space and a bar.

The numerical codes are more tricky. Sometimes they are in the above format but in some cases they are together eg 51;63 but these will also be within the bars.
 
Upvote 0
Yes. Mostly preceded by a bar and a space and followed by a space and a bar.
Mostly? I hate qualifying words like that. What other possibilities are there?

And, in addition to the question above, double checking... your mostly is "a bar and a space" before and "a space and bar" after... in that order?
 
Upvote 0
Sorry. I should have been more specific. The codes beginning with a letter are always in this format. The numerical ones are the same with the difference being that sometimes there are 2 numbers separated by a semicolon.
 
Upvote 0
And to clarify, the 2 numbers can be any of the ones listed. With the lower number first in each case if that helps.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top