Extracting Multiple Text from a Single Cell

[GoGoeGo]

I am trying to pull out from this string of text anything between the "~1c".

~1cE~1c****************~1c101~1c100~1c~1c~1c~1c~1c~1c~1c~1c22~1c01424~1c00000~1c0007435~1c0007435~1c079~1c0003944~1c0002239~1c000000~1c0000883~1c070~1c0002000~1c0005215~1c000398~1c0000645~1c070~1c0002000~1c0005215~1c000398~1c0000645~1c081~1c0001000~1c0020400~1c000000~1c0002040~1c) N(7,ABSMLT1)

Is there an easy way to convert this text to look like this?:
E
****************
101
100
c22
c01424
1c00000
-and so on-

[JohnTopley]

With data in A1

in A2 and copy down

=TRIM(MID(SUBSTITUTE($A$1,"~1c",REPT(" ",99)),(ROWS($1:1)-1)*99+1,99))

you will get blank cells where there are multiple "~1c" entries

[GoGoeGo]

Worked like a charm! Thank you!!

[GoGoeGo]

Hi JohnTopley - Could you explain why you chose to use 99? The formula works up to a certain degree. Then it breaks one of the numbers in half. I've highlighted where it breaks in yellow and changed the font to red in the string.

[JohnTopley]

Try

=TRIM(MID(SUBSTITUTE($A$1,"~1c",REPT(" ",256)),(ROWS($1:1)-1)*256+1,256))

which hopefully should cater for your sample.

[GoGoeGo]

Thanks so much, that did fix it. Could you tell me why you chose 256? I'm trying to get an understanding of how to use the formula rather than just using it.