Extracting data from string

[Mo]

I'd like to extract data from a string variable. The data mostly looks
like this: '456 585 8975 / MSKI 105'. An added complication is that the
second part of the string does not have a consistent format. The format
of the first part is always the same.

I need to extract into two parts either side of the '/'. I can do this
easily enough, for example, to extract first part:

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

And the second part:

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 '/'.

I tried using the 'Format' function to do this, but with no success so
far. Can anyone help?

TIA

 

[Stefan Hoffmann]

hi Mo,

However, some of the data will contain only the first part of the string
('456 585 8975'), or the second (' MSKI 105') without the '/'.

Use IIf():

IIf(InStr([yourfield], "/") > 0, <bothParts>, <onlyOnePart>)


mfG
--> stefan <--

 

[Mo]

hi Mo,

However, some of the data will contain only the first part of the string
('456 585 8975'), or the second (' MSKI 105') without the '/'.

Use IIf():

IIf(InStr([yourfield], "/") > 0, <bothParts>, <onlyOnePart>)


mfG
--> stefan <--

Thanks for your reply. I'm not sure what you mean by <bothparts> and
<onlyonepart>

Can you clarify please?

 

[Stefan Hoffmann]

hi Mo,

Thanks for your reply. I'm not sure what you mean by <bothparts> and
<onlyonepart>

Can you clarify please?

You insert your working snippets there as you need it.


mfG
--> stefan <--

 

[John Spencer]

FirstPart:
IIF([NHS Number new/old] LIKE "### ### ####*",LEFT([NHS Number new/old],12), Null)

SecondPart:
More complex since you need to test for two conditions
IIF([NHS Number new/old] Like "*/*"
, Trim(Mid([NHS Number new/old],Instr(1,[NHS Number new/old],"/")+1)),
IIF([NHS Number new/old] NOT LIKE "### ### ####", [NHS Number new/old],Null))

This does make the assumption that the NEW NHS number always has the format
nnn nnn nnnn.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Mo]

FirstPart:
IIF([NHS Number new/old] LIKE "### ### ####*",LEFT([NHS Number
new/old],12), Null)

SecondPart:
More complex since you need to test for two conditions
IIF([NHS Number new/old] Like "*/*"
, Trim(Mid([NHS Number new/old],Instr(1,[NHS Number new/old],"/")+1)),
IIF([NHS Number new/old] NOT LIKE "### ### ####", [NHS Number
new/old],Null))

This does make the assumption that the NEW NHS number always has the
format nnn nnn nnnn.

Thanks very much John. I'll try that.