Selected Answer
This is the formula which does the job.
[Row 3] =TRIM(MID(TRIM(MID($D3,FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),3))-LEN("DATE;"))),4,100))
At the heart of the above formula is this function. It substitutes 3rd occurrence of a semicolon with CHR(160) - which doesn't normally occur in text - and returns the position of that character. The critical argument is the 3 at the very end which determines that it is the position of the 3rd occurrence that you want.
$D3,FIND(CHAR(160),SUBSTITUTE($D3,";",CHAR(160),3))
This works because the text you have seems to be computer generated, separating lines by semicolons. The account number you want is in the 4th line. Logically, the 4th line is between the 3rd and 4th occurrence of a semicolon in the text.
Further with this logic, the line you want is the MID(String) starting at the 3rd occurrence of ";" +1 (so as to exclude the semicolon itself) and continuing for the distance between the 3rd and 4th occurrence of ";". That line would include the word "DATE followed by a semicolon. So the formula might just specify -5 for the length of the desired string. To make it more transparent I defined this number as LEN("DATE;") which always evaluates to 5.
The result of that part of the formula is something like this: " 436 0158928604 ". Observe the leading and trailing blanks. The TRIM() function removes them. That is the inside TRIM which leaves a string like this: "436 0158928604" from which the formula then removes the first 4 characters and trims the result once again. The 100 toward the end of the formula specifies the maximum length of output. Actually, there are only 10 or 11 characters in the string remaining at that point.
The final step (removing 4 characters) is illogical to some extent because if the branch code could ever have more or less than 3 characters followed by a blank the returned account number would be wrong. The formula should be tweaked to accommodate that possibility. However, based on this reliance on format, one might perhaps also assume that the account number always has 10 digits. In that case the entire formula might be simplified as shown below.
=TRIM(MID($D4,FIND(CHAR(160),SUBSTITUTE($D4,";",CHAR(160),3))+5,10))
Edit 06 Sep 2018 ==================================
I don't like yesterday's solution anymmore because it presumes that the account number will alsoways be in the 3rd line which will always be followed by the "DATE". The formula below takes a more direct approach. It looks for the line identified as "YOUR REF NO;". In its simplest form, given below, the account number will be 17 characters to the right of that point and 10 characters long.
=MID($D3,FIND("YOUR REF NO;",$D3)+17,10)
If "YOUR REF NO;" (case sensitive) isn't found an error will occur. The expanded formula below gives notice.
=IFERROR(MID($D3,FIND("YOUR REF NO;",$D3)+17,10),"Ref not found")
Please take all of the above as a starting point for your considerations. The solution you finally deploy must rely on the uniformity of your received data. The above merely shows how the section that contains the account number can be identified. Once that is accomplished there are many ways to extract the account number from the section. Please don't hesitate to ask once you know more facts.
