Data Arrives as Text, struggling to "normalise" it

[DominicJ]

I receive some information from an outside agency, this arrives as a PDF text file and they will not change that.

I've copied and pasted it into Excel, and it all populates col A, but it does at least break into the correct rows

I used Text to Columns on " " to break it into columns, but the data isnt consistant.

The First Col, contains the Reference Number, which I need, and can get.

The Next Two to Four Columns Contain the Name. Now I managed to get the the name by using "IF(IS number" in a fairly creative manner
But I dont need the name, or the address.

The next piece of Data I need, it the Opening Date, which varies from Cell K-N, the Closing Date, from Cell L-O and the value, which will be M-P

These three will be sequential

Now I've written it out, I'm thinking I could just ask If P is a number, and if it is, not, ask if O is, and if thats not, ask if N is?

Oh well,
I'll update if that cracks it.

---------- Post added at 03:55 PM ---------- Previous post was at 03:03 PM ----------

=IF(ISNUMBER('Step 2'!P40),'Step 2'!P40,IF(ISNUMBER('Step 2'!O40),'Step 2'!O40,IF(ISNUMBER('Step 2'!N40),'Step 2'!N40,'Step 2'!M40)))

=IF(ISNUMBER('Step 2'!P40),'Step 2'!O40,IF(ISNUMBER('Step 2'!O40),'Step 2'!N40,IF(ISNUMBER('Step 2'!N40),'Step 2'!M40,IF(ISNUMBER('Step 2'!M40),'Step 2'!L40,'Step 2'!K40))))

=IF(ISNUMBER('Step 2'!P40),'Step 2'!N40,IF(ISNUMBER('Step 2'!O40),'Step 2'!M40,IF(ISNUMBER('Step 2'!N40),'Step 2'!L40,IF(ISNUMBER('Step 2'!M40),'Step 2'!K40,'Step 2'!K40))))

Yep, that was easy, oh well