Extracting parts of string (1 Viewer)

[majhl]

I need to extract data from a string into two new variables (called 'NHSNew' and 'NHSOld'). The string is mostly separated by a '/'. Data to the left of the string goes into 'NHSNew' and data to the right into 'NHSOld'. I can do this first part OK as shown below.

Data to the left of the '/':

IIf (InStr ([NHS Number new/old],"/"), Left ([NHS Number new/old], InStr([NHS Number new/old],"/")-1))

Data to the right of the '/':

IIf (InStr ([NHS Number new/old],"/"), LTrim (Mid ([NHS Number new/old], 15)))

However, some of the data will contain only the first part of the string ('456 585 8975'), or the second ('MSKI 105') without the '/'. An added complication is that the second part of the string does not have a consistent format. In these cases, how do I extract the relevant data into the correct variable?

TIA

 

[DCrake]

A custom function will handle this.

First you need to test if there is a / in the whole string
If not is the strings first three characters numeric
If so then this is the NHS number, and as nhs numbers are now 10 digits you will need the first 12 characters (10 digits 2 spaces)
And if we know that the / is in the 13 postion then everything after that is the second element. Thus Mid(Anystring,14) will give you the second part.

If the first three characters are not numbers (MSK) then this will be the second element.

Now you have identified one or both of the elements then you need to pass the correct one back to the user.

So
X = GetMyString("123 456 7890/MSKI 105",1)
will return then nhs number
X = GetMyString("123 456 7890/MSKI 105",2)
will returnn the hosp no

Alls you need to do is to write the function.

 

[majhl]

Thanks very much for the response David. Your explanation really cleared things up in my head!