Leo_Polla_Psemata
Registered User.
- Local time
- Today, 03:58
- Joined
- Mar 24, 2014
- Messages
- 364
Yes, there are more spaces between blocks of characters and yes, there may be three spaces
Best string Function (1 Viewer)
- Thread starter Leo_Polla_Psemata
- Start date
Yes, there are more spaces between blocks of characters and yes, there may be three spaces
- Local time
- Today, 03:58
- Joined
- Oct 29, 2018
- Messages
- 21,471
Sorry, I misread the question. Are there more than one double spaces in the text?
Gasman
Enthusiastic Amateur
- Local time
- Today, 11:58
- Joined
- Sep 21, 2011
- Messages
- 14,288
If you put the string within code blocks it would probably respect the spaces. The site removes characters now and again.
HTH
Code:
? Mid("123456789abc defg hzzzzzzz",10,instr(10, "123456789abc defg hzzzzzzz"," ")-10)
abc defgHTH
Leo_Polla_Psemata
Registered User.
- Local time
- Today, 03:58
- Joined
- Mar 24, 2014
- Messages
- 364
Okay
Here you are a real example
001FCIU7196282NL572013 4510NFCY-CY N000840CT 11123504000000065380 GC11123500L572013
I have underlined or bold the pieces of string i want to separate
FCIU7196282 is the container number
L572013 is the seal number of the container, it may be longer but always leave at least two spaces before jump to next block of characters.
CY-CY is the movement type
4510 is the iso type
000840 is the number of packages
CT is the type of packages (cartons bags boxes)
11123 is the weight in kg
500 is the decimal (this container cargo weight is 11123.500)
Here you are a real example
001FCIU7196282NL572013 4510NFCY-CY N000840CT 11123504000000065380 GC11123500L572013
I have underlined or bold the pieces of string i want to separate
FCIU7196282 is the container number
L572013 is the seal number of the container, it may be longer but always leave at least two spaces before jump to next block of characters.
CY-CY is the movement type
4510 is the iso type
000840 is the number of packages
CT is the type of packages (cartons bags boxes)
11123 is the weight in kg
500 is the decimal (this container cargo weight is 11123.500)
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 18:58
- Joined
- May 7, 2009
- Messages
- 19,242
not so elegant
Code:
Private Sub t()
Dim s As String
Dim ID As String
Dim CONTAINER_NO As String
Dim SEAL_NO As String
Dim ISO_TYPE As String
Dim MOVEMENT_TYPE As String
Dim NUMBER_OF_PACKAGE As String
Dim PACKAGE_TYPE As String
Dim WEIGHT As Double
s = "001FCIU7196282NL572013 4510NFCY-CY N000840CT 11123504000000065380 GC11123500L572013"
ID = Left(s, 3)
s = Trim(Mid(s, 4))
CONTAINER_NO = Left(s, InStr(1, s, "N") - 1)
s = Trim(Replace(s, CONTAINER_NO & "N", "", 1, 1))
SEAL_NO = Trim(Left(s, InStr(1, s, " ")))
s = Trim(Replace(s, SEAL_NO, "", 1, 1))
ISO_TYPE = Left(s, InStr(1, s, "N") - 1)
s = Trim(Replace(s, ISO_TYPE & "NF", "", 1, 1))
MOVEMENT_TYPE = Trim(Left(s, InStr(1, s, " ")))
s = Trim(Replace(s, MOVEMENT_TYPE, "", 1, 1))
s = Replace(s, "N", "", 1, 1)
NUMBER_OF_PACKAGE = getNumberOfPackage(s)
s = Trim(Replace(s, NUMBER_OF_PACKAGE, "", 1, 1))
PACKAGE_TYPE = Left(s, 2)
s = Trim(Replace(s, PACKAGE_TYPE, "", 1, 1))
s = Trim(Replace(s, SEAL_NO, "", 1, 1))
WEIGHT = getWeight(s)
Debug.Print "ID = " & ID
Debug.Print "CONTAINER NUMBER = " & CONTAINER_NO
Debug.Print "SEAL NUMBER = " & SEAL_NO
Debug.Print "ISO TYPE = " & ISO_TYPE
Debug.Print "MOVEMENT TYPE = " & MOVEMENT_TYPE
Debug.Print "NUMBER OF PKG = " & NUMBER_OF_PACKAGE
Debug.Print "PACKAGE TYPE = " & PACKAGE_TYPE
Debug.Print "WEIGHT = " & WEIGHT
End Sub
Public Function getWeight(sString As String) As Double
Dim s As String
Dim sRet As String
Dim i As Integer
For i = Len(sString) To 1 Step -1
s = Mid(sString, i, 1)
If IsNumeric(s) Then
sRet = s & sRet
Else
Exit For
End If
Next
If Len(sRet) > 0 Then
getWeight = Val(sRet) * 0.001
End If
End Function
Public Function getNumberOfPackage(sString As String) As String
Dim s As String
Dim sRet As String
Dim i As Integer
For i = 1 To Len(sString)
s = Mid(sString, i, 1)
If IsNumeric(s) Then
sRet = sRet & s
Else
Exit For
End If
Next
If Len(sRet) > 0 Then
getNumberOfPackage = sRet
End If
End Function
