Solved Extracting part of string

[quest]

Hi,
I have field [Transfer] (text field) like this for example 349/2016 79/2019 i want to get four parts like 349, 2016, 79 and 2019. i got first and can get the last too but how to get 2016 and 79. one more thing if field is only 349/2016 i need only 349 and 2016 so what i will get for part three and four. i used something like this for first part
Part1: Left([Transfer];InStr([Transfer];"/")-1)

 

[tvanstiphout]

I would do a double Split on this: first Split on the space, then Split the resulting items on the forward slash.
Look up the Split function in Help.

 

[quest]

I would do a double Split on this: first Split on the space, then Split the resulting items on the forward slash.
Look up the Split function in Help.

I never used split function before but i think it don't work in query right?

 

[jdraw]

There is an example and discussion Split in a query here.

 

[KitaYama]

but how to get 2016 and 79.

The same way you did for 349 and 2019

SELECT
    Transfer,
    
    Left(Transfer, InStr(Transfer, "/") - 1) AS Part1,
    Mid(Transfer, InStr(Transfer, "/") + 1, InStr(Transfer, " ") - InStr(Transfer, "/") - 1) AS Part2,
    
    Mid(Transfer, InStr(Transfer, " ") + 1, InStrRev(Transfer, "/") - InStr(Transfer, " ") - 1) AS Part3,
    Mid(Transfer, InStrRev(Transfer, "/") + 1) AS Part4

FROM
    YourTableName;

one more thing if field is only 349/2016 i need only 349 and 2016 so what i will get for part three and four.

If your data in the table is not consistent, you can add IIF to above to return a null value.

SELECT
    Transfer,
    
    Left(Transfer, InStr(Transfer, "/") - 1) AS Part1,
    
    Mid(Transfer, InStr(Transfer, "/") + 1,
        IIf(InStr(Transfer, " ") > 0, InStr(Transfer, " ") - InStr(Transfer, "/") - 1, Len(Transfer))) AS Part2,
    
    IIf(InStr(Transfer, " ") > 0,
        Mid(Transfer, InStr(Transfer, " ") + 1, InStrRev(Transfer, "/") - InStr(Transfer, " ") - 1),
        Null) AS Part3,
    
    IIf(InStrRev(Transfer, "/") > InStr(Transfer, " "),
        Mid(Transfer, InStrRev(Transfer, "/") + 1),
        Null) AS Part4

FROM
    YourTableName;

 

[Pat Hartman]

It is always poor practice to store data mushed like this. Also, might there ever be more than 4 parts?

 

[CJ_London]

Since these are all numbers, you can use the val function

Val(transfer) returns 349

Val(mid(transfer, instr(transfer,”/“)+1) returns 2016

Val(mid(transfer, instr(transfer,” “)+1) returns 79

Val(mid(transfer, instrrev(transfer,”/“)+1) returns 2019

 

[quest]

Since these are all numbers, you can use the val function

Val(transfer) returns 349

Val(mid(transfer, instr(transfer,”/“)+1) returns 2016

Val(mid(transfer, instr(transfer,” “)+1) returns 79

Val(mid(transfer, instrrev(transfer,”/“)+1) returns 2019

thank you and there are always maximum four parts can be two or four parts. I tried yours too but two, three and four don't work

 

[jdraw]

So, why is the thread marked SOLVED? The solution may help someone else.
I tried a few things. Major assumption--your field [transfers] can only have 1 space char between numbers eg. 34_567/45/2029 45_5687_42/3045.
There can be 2, 3, or 4 number sequences in Transfer

Test data:

transfer
349/2016 79/2019
3/4 987/ 2024
345/4567
25/456 738
365 54 678/ 987

tblAWFJun24

A function

Function XSplit(fld As String, delim As String) As String
          Dim varray As Variant, res As String
          Dim i As Long
          'assumption/condition:   only 1 space char between number sequences
         
          'ensure last char is a "/"
10        If Right(fld, 1) <> "/" Then fld = fld & "/"
         
          'convert a space to a delim character
20        fld = Replace(fld, " ", "/")
         
          'remove "/ " and " /" and "//" combinations
30        fld = Replace(fld, " /", "/")
40        fld = Replace(fld, "/ ", "/")
50        fld = Replace(fld, "//", "/")
          'at this point all number sequences are delimited
          'so use split
60        varray = Split(fld, delim)
70        For i = 0 To UBound(varray) - 1
80          res = res & varray(i) & ", "
90        Next
100       XSplit = Mid(res, 1, Len(res) - 2)
End Function

A query

SELECT Xsplit([transfer],"/") AS RESult
FROM tblAWFJun24;

Test results

RESult
349, 2016, 79, 2019
3, 4, 987, 2024
345, 4567
25, 456, 738
365, 54, 678, 987

Query63

 

[quest]

So, why is the thread marked SOLVED? The solution may help someone else.

i made modification of example kitayama send and it works. response from Cj_London came after that and i looked at it (always can learn something).

 

[CJ_London]

but two, three and four don't work

was done freehand on my phone - so don't know what you actually tried - but was missing an end bracket which would have generated an error message. Also because on the phone, there were smart quotes which need to be changed to regular quotes.

Once those corrections are in place, 3 won't without a small adjustment, but 2 and 4 do - in the immediate window

?val("349/2016 79/2019")
349

?Val(mid("349/2016 79/2019", instr("349/2016 79/2019","/")+1))
201679
correct to
?Val(mid("349/2016 79/2019", instr("349/2016 79/2019","/")+1,4))
2016

?Val(mid("349/2016 79/2019", instr("349/2016 79/2019"," ")+1))
79

?Val(mid("349/2016 79/2019", instrrev("349/2016 79/2019","/")+1))
2019

 

[quest]

was done freehand on my phone - so don't know what you actually tried - but was missing an end bracket which would have generated an error message. Also because on the phone, there were smart quotes which need to be changed to regular quotes.

Once those corrections are in place, 3 won't without a small adjustment, but 2 and 4 do - in the immediate window

?val("349/2016 79/2019")
349

?Val(mid("349/2016 79/2019", instr("349/2016 79/2019","/")+1))
201679
correct to
?Val(mid("349/2016 79/2019", instr("349/2016 79/2019","/")+1,4))
2016

?Val(mid("349/2016 79/2019", instr("349/2016 79/2019"," ")+1))
79

?Val(mid("349/2016 79/2019", instrrev("349/2016 79/2019","/")+1))
2019

i made them like this
Left(Transfer;InStr(Transfer;"/")-1)
Mid(Transfer;InStr(Transfer;"/")+1;IIf(InStr(Transfer;" ")>0;InStr(Transfer;" ")-InStr(Transfer;"/")-1;Len(Transfer)))
IIf(InStr(Transfer;" ")>0;Mid(Transfer;InStr(Transfer;" ")+1;InStrRev(Transfer;"/")-InStr(Transfer;" ")-1);Null)
IIf(InStrRev(Transfer;"/")>InStr(Transfer;"/");Mid(Transfer;InStrRev(Transfer;"/")+1);Null)

 

[quest]

was done freehand on my phone - so don't know what you actually tried - but was missing an end bracket which would have generated an error message. Also because on the phone, there were smart quotes which need to be changed to regular quotes.

Once those corrections are in place, 3 won't without a small adjustment, but 2 and 4 do - in the immediate window

?val("349/2016 79/2019")
349

?Val(mid("349/2016 79/2019", instr("349/2016 79/2019","/")+1))
201679
correct to
?Val(mid("349/2016 79/2019", instr("349/2016 79/2019","/")+1,4))
2016

?Val(mid("349/2016 79/2019", instr("349/2016 79/2019"," ")+1))
79

?Val(mid("349/2016 79/2019", instrrev("349/2016 79/2019","/")+1))
2019

no error mistake this time but return zero for 2, 3 and 4

 

[CJ_London]

As you can see from my examples from the immediate window, it provides the required results. If you are getting 0 then something is different

However you have a working solution so little point in you trying to adopt a different one

 

[quest]

As you can see from my examples from the immediate window, it provides the required results. If you are getting 0 then something is different

However you have a working solution so little point in you trying to adopt a different one

Thank you.