Removing Repeating Blocks of text

[Helen842000]

Hi,

I have a very long excel spreadsheet (46K rows) Made up mostly of a repeated text block & then a list of serial numbers, then the same text block and different serial numbers. This repeats over and over. I just want the serial numbers and to remove the text part.

I get an error when I try to do Find & Replace - the error is "formula is too long". I presume this is because the text in the cells is around is two paragraphs.

I presume I'll need to use VBA to clear this repeating text out or use the SUBSTITUTE function.

Can anyone suggest a suitable method?

Thanks!

Example Spreadsheet


Dear Mr Blogs, TEXT BLOCK STARTS XXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CONTINUES FOR 2 PARAGRAPHS THEN BLOCK ENDS X

XYZ123
LMN052
45195A

Dear Mr Blogs, TEXT BLOCK STARTS XXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CONTINUES FOR 2 PARAGRAPHS THEN BLOCK ENDS X

912ZZZ
456232

(This repeats around 300 times, I have around 300 blocks of identical text to remove)

 

[Marcol]

Maybe this?

With your data in A2 Down
In B2

=IF(LEN(A2)<>6,"",ROW())

Drag/Fill Down

In C2

=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))

Drag/Fill Down until blanks are returned

Copy and paste > paste special > values to your destination if required

Change the IF() condition to suit your code string lengths if they aren't all 6 characters long.
e.g.

=IF(OR(A2="",LEN(A2)>10),"",ROW())