Extracting data from string

  • Thread starter Thread starter Mo
  • Start date Start date
M

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
 
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 '/'.
Click to expand...
Use IIf():

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


mfG
--> stefan <--
 
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 '/'.
Click to expand...
Use IIf():

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


mfG
--> stefan <--
Click to expand...

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

Can you clarify please?
 
hi Mo,

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

Can you clarify please?
Click to expand...
You insert your working snippets there as you need it.


mfG
--> stefan <--
 
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
 
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.
Click to expand...

Thanks very much John. I'll try that.
 
Back
Top