Combining Text split across uneven number of rows

[DominicJ]

Slightly More Complicated this time.

I receive some data from an outside agency in a PDF, they refuse to send me the excel sheet, they insist on typing it up.

I've copied this PDF into excel, but its splitting the line of text across two, sometimes three rows

The final row, will always start "Claim"

If did an B1=left(A1,5) to pull out if its a claim or not, and then did C3=IF(B3="claim",A1&A2&A3,"")

But that doesnt work for two row claims, just three row

I'm thinking I can do an C3=If(B3="claim",If(B1="claim" to determine if its a two or three row entry.

Does that sound like it will work?
Is there a better way?

[arlu1201]

It will be good if you attach a sample file.

[DominicJ]

Cant really attach a file, its all data protected.

But here goes

Pork Sausages
Beef Sausges
Claim
Pork Sausages
Claim
Beef Sausges
Claim
Pork Sausages
Beef Sausges
Claim

If you copy that into cells A1:A10
I then use Left(5) to pull out claim (And "Pork ", and "Beef " into Cols B1:10)

I now want C3 to return "Pork Sausages Beef Sausages Claim"
Which I can do

But I want C5 to return "Pork Sausages Claim", instead I am getting "Claim Pork Sausages Claim"


=IF(B5="claim",A3&A4&A5,)
Works Fine, for three row problems.

If its a two row problem, I only need cells A4 and A5.
I thought i could solve this, by asking
=IF(B5="claim",IF(B3=”claim”,A4&A5,A3&A4&A5),A3&A4&A5)

But it doesnt like the second claim, throws it out as an error

I suppose I could accept "Claim Pork Sausages Claim" and use Column D pull out everything after the first claim, but I'm hoping there is a more elegant way (and I didnt think of that till right then)

---------- Post added at 10:01 AM ---------- Previous post was at 09:34 AM ----------

I've done it that way
So Col B pulls out Claim
Col C finds "claim" in B, and pulls out three rows
Col D finds "claim in B and pulls out two rows.
Col E pulls out the first 5 from the left of C
Col F checks E, and if its Claim, I know its a two row problem, so can pull out D5, otherwise, C5

B3=LEFT(A3,5)
C3=IF(B3="claim",A1&A2&A3,"")
D3=IF(B3="Claim",A2&A3,"")
E3=LEFT(C3,5)
F3=IF(E3="Claim",D3,C3)

If anyone has a prettier way, I'm happy to be informed

[martindwilson]

i can extract what you want but not quite where you wanted it see attached

[DominicJ]

Fantastic
Thanks