By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,479 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Find in string and

P: 34
Hi,

Would anyone now how to write an instr that would grab only the date from the string below (i.e between the 2 colons)

I have tried multiple times to get this to work to no avail, the colons will NOT always be at character 14, so need to find first ":" then grab the next 8 caracters.

USINAS SID MI:30/12/09:0.3072:85

i would like to achieve the result

30/12/09

so far I have tried using Left with instr and Right - getting very frustrated.

Thanks in advance

Nigel
Jan 12 '10 #1

✓ answered by MMcCarthy

Try this one ...

Expand|Select|Wrap|Line Numbers
  1. Right(Left("USINAS SID MI:30/12/09:0.3072:85", InStr(1, "USINAS SID MI:30/12/09:0.3072:85", "/", vbTextCompare) + 5), 8)
  2.  

Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this one ...

Expand|Select|Wrap|Line Numbers
  1. Right(Left("USINAS SID MI:30/12/09:0.3072:85", InStr(1, "USINAS SID MI:30/12/09:0.3072:85", "/", vbTextCompare) + 5), 8)
  2.  
Jan 12 '10 #2

P: 34
Thanks msquared,
any chance I could put this in a query, dont really want to create a function but will do if it is the best way. I only need to perform this once a day on import for about 50 rows and was trying to just put an make table query on it.
Thanks
Jan 12 '10 #3

P: 34
no worries - got it to work - just took the vbtextcompare out. Thanks for this saved me a lot of time
Jan 12 '10 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You're welcome, glad you got it to work.
Jan 12 '10 #5

nico5038
Expert 2.5K+
P: 3,072
An alternative might be a function:
Expand|Select|Wrap|Line Numbers
  1. Function fncSplit(strInput As String)
  2.    Dim arr
  3.    arr = Split(strInput, ":")
  4.    fncSplit = arr(1)
  5. End Function
  6.  
In a query you can use:
Expand|Select|Wrap|Line Numbers
  1. SELECT fncSplit([Field with colons]), ... FROM tblX
  2.  
Nic;o)
Jan 12 '10 #6

P: 34
Hi Guys,

Thanks you both for your answers, both work well, have a quick question, tried to do it myself but can this be modified to dismiss any fields that do not contain any ":" ? - at the moment it either returns the first 8 char with first option, or using the Function it will Error. I have tried writing an imbedded IF statement (i.e. IF(Instr[filed1],1=";", etc ) but cannot solve this. Appreciate the help Nic, Msquared.
Thanks
Jan 13 '10 #7

nico5038
Expert 2.5K+
P: 3,072
Try
Expand|Select|Wrap|Line Numbers
  1. Function fncSplit(strInput As String)
  2.  
  3.     Dim arr
  4.  
  5.     if Instr(strInput,":") > 0 then
  6.        arr = Split(strInput, ":")
  7.        fncSplit = arr(1)
  8.     else
  9.        'Empty string , or whatever you want to return
  10.        fncSplit = ""
  11.     end if
  12.  
  13. End Function
  14.  
Nic;o)
Jan 13 '10 #8

P: 34
Thanks Nic, should have known that myself - been staring at this far to long - going to take a break I think. Thanks again
Jan 13 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.