Find a variable substring and set it to a variable

[Lego C130]

Hello Excel Gurus,

I’d like to simplify a process at work. Here’s an example of my raw data:

AMSNDAT/N/0346/-/--/REC/-/-/DEPLOC:KDYS/010800ZJAN/ARRLOC:KDYS/011200ZJAN//MSNACFT/1/ACTYP:A1C/AEGIS10/-/-/AB45/-/-/-/-/B:11223//AMSNLOC/010830ZJAN/010845ZJAN/12JAP/100//

If someone could help out with the code for numbers 1-3, I can apply your example to the rest of my problem sets.

Here’s what I’d like to do with it:

1. Find the “ACTYP” and assign it to a variable (the characters between “ACTYP:” and “/AEGIS10”; will be a variable number of characters).
2. Find the “start date” and assign it to a variable. The start date in the data above is after “AMSNLOC/”. The data 010830ZJAN means 01 JAN at 0830 so the date here is 01 JAN.
3. Find the “start time” and assign it to a variable. The start time in the data above is 0830 as mentioned in #2.
4. Find “end date” and assign it to a variable. The end date in the data above is in the string “010845ZJAN” (immediately after the data from #2 and #3 above). It won’t always be the same date. In this example the date is still 01 JAN.
5. Find the “end time” and assign it to a variable. The end time in #4’s example is 0845.
6. Find the “tag” and assign it to a variable. The tag in the data above immediately follows the “end date/time” data; 12JAP.
   
   Thanks in advance, I appreciate your time. Jeff

 

[bschwartz]

Try these out:

1. =REPLACE(REPLACE(A1,FIND("/AEGIS10",A1),LEN(A1),""),1,FIND("ACTYP:",A1)+5,"")
2. =DATEVALUE(RIGHT(REPLACE(A1,FIND("AMSNLOC/",A1)+10,LEN(A1),""),2)&RIGHT(REPLACE(A1,FIND("AMSNLOC/",A1)+18,LEN(A1),""),3))
3. =TIMEVALUE(MID(A1,FIND("AMSNLOC/",A1)+10,2)&":"&MID(A1,FIND("AMSNLOC/",A1)+12,2))

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean for the first 3? If not, more details please.

Sub Extract()
  Dim sACTYP As String, sStartDate As String, sStartTime As String, sDateTime As String

  Const s As String = "AMSNDAT/N/0346/-/--/REC/-/-/DEPLOC:KDYS/010800ZJAN/ARRLOC:KDYS/011200ZJAN//MSNACFT/1/ACTYP:A1C/AEGIS10/-/-/AB45/-/-/-/-/B:11223//AMSNLOC/010830ZJAN/010845ZJAN/12JAP/100//"
  
  sACTYP = Split(Split(s, "ACTYP:")(1), "/")(0)
  sDateTime = Split(Split(s, "AMSNLOC/")(1), "/")(0)
  sStartDate = Left(sDateTime, 2) & " " & Right(sDateTime, 3)
  sStartTime = Mid(sDateTime, 3, 4)

End Sub

 

[Lego C130]

Thanks to both of you for jumping on this quickly. The problem I'm going to run into is the data I get (military) is always going to have different lengths, spacing and a host of other issues. I think this problem is one I learn more about VB on and it's probably going to take a while. I appreciate your help.

 

[Peter_SSs]

The problem I'm going to run into is the data I get (military) is always going to have different lengths, spacing and a host of other issues.

I guess it depends a bit on how much variation there is in your data, but my suggestion relies little on lengths.

1. For ACTYP it finds whatever is after "ACTYP:" and before the next "/" no matter how long or short that intermediate text is.

For the date time parts it first extracts whatever text is between "AMSNLOC/" and the following "/" sign (010830ZJAN in this example) then...

2. For Start Date, takes the first 2 characters and the last 3 characters and puts a space between to get "01 JAN".

3. For Start Time, takes characters 3,4,5 & 6 to get "0830".

Aren't these the expected results?

Or does this code fail on other data you have? If some more sample data and expected results would help.

 

[Lego C130]

Thanks Peter. Sorry for the delayed response, I've been away the last couple days. Your two responses really helped me and I'm on my way to solving the larger problem. I appreciate your time. Thanks, Jeff.