Retreiving data between some characters (1 Viewer)

example : 1234568595455 ED060 15122017 1A4S856DJKJ

if the above data is under Field 1 , I would like to see the result as ED060 15122017. Can you help me how to achieve this result.

make this custom function, put it in a module.
then you can run a query and pull the data from inside.

Usage: select getInnerWord([Field]) from table


note: i did not account for nulls

There are many different ways to do this but it's unlikely that many would work for all of your data.

Does all of the Field 1 data begin with 1234568595455 and end with 1A4S856DJKJ ?

If not, how do you know which data needs to be ignored?

Other examples would also be helpful

am not familiar with Module :-(

Ranman256 said:

make this custom function, put it in a module.
then you can run a query and pull the data from inside.

Usage: select getInnerWord([Field]) from table

Code:Copy to clipboard

function getInnerWord(pvField)
dim i as integer, j as integer

i = instr(pvField," ")
j = instrRev(pvField," ")
getInnerWord= mid(pvField,i,j-i)
end function

note: i did not account for nulls

Click to expand...

no character might change, its not constant nor the position is constant

Peter Reid said:

There are many different ways to do this but it's unlikely that many would work for all of your data.

Does all of the Field 1 data begin with 1234568595455 and end with 1A4S856DJKJ ?

If not, how do you know which data needs to be ignored?

Other examples would also be helpful

Click to expand...

Peter Reid said:

There are many different ways to do this but it's unlikely that many would work for all of your data.

Does all of the Field 1 data begin with 1234568595455 and end with 1A4S856DJKJ ?

If not, how do you know which data needs to be ignored?

Other examples would also be helpful

Click to expand...

I just need the date after ED060

Why is this data all in 1 field?
Is it always ED060?

Suggest you give us a few more examples to show some variations in your data.

jdraw said:

why is this data all in 1 field?
Is it always ed060?

Suggest you give us a few more examples to show some variations in your data.

Click to expand...

eus81375wal11a1ax442117112115211211"ed111 4"ed114a cc"ed115 14"ed116 91"ed117 25182117"ed118a 111111111215135611"ed311 1121513561"ed312a 1183769688"ed132 %"ed111 dl "ed113a 1111311111111"ed115a 1111311111111"ed121 25182117"ed123 a"ed124a 25172117"ed125a 24182117"ed060 25182117"ed311 1131"ed135 64"ed171 1551111"ed211 25111111111"ed234 1
eus81375wal11a1ax442117112728111211"ed111 4"ed114a cc"ed115 14"ed116 91"ed117 25172117"ed118a 111111111191613311"ed311 1119161331"ed312a 1185316288"ed132 %"ed111 dl "ed113a 1111311111111"ed115a 1111311111111"ed121 25172117"ed123 a"ed124a 26162117"ed125a 24172117"ed131 25172117"ed311 1129"ed135 64"ed171 1551111"ed211 25111111111"ed234 1
eusg78197ab99a1ba7x2117117811911 11"ed111 4"ed114a cc"ed115 14"ed116 91"ed117 17192117"ed118a 111111111174853771"ed311 1117485377"ed312a 1345715271"ed132 %"ed111 dl "ed060 17192117"ed123 a"ed124a 17182117"ed125a 16192117"ed131 17192117"ed311 1131"ed135 11"ed171 1551111"ed211 25111111111"ed234 1


these are three line data....I need the result in case the line has ED060 and the date next this value.

This will give you the 8 characters after edo060 if it appears in your field (change YourFieldName to whatever your field is called)

IIf(InStr([YourFieldName],"ed060 ")>0,Mid([YourFieldName],InStr([YourFieldName],"ed060 ")+6,8))

peter reid said:

this will give you the 8 characters after edo060 if it appears in your field (change yourfieldname to whatever your field is called)

iif(instr([yourfieldname],"ed060 ")>0,mid([yourfieldname],instr([yourfieldname],"ed060 ")+6,8))

Click to expand...

perfect !! That worked