• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code is not handling a certain type of text string.

I have a code that used to work and it worked great till certain type of string came to process. What this code is expected to do ? let me explain

This code search for the last set of numeric numbers in a text string and then try to make ranges out of those last set of numbers. In case any suffix was there in string, it again add that to the ranges. i have attached a sample sheet. in sheet 1 Green cells are showing the intended working of code but it is failing for the red cell strings.

Code:
Sub Traiter()
Const Dest = "G1"                                  'Where write result
Dim LastLig As Long, i As Long, k As Long, io As Long
Dim StrIni As String, StrFin As String
Dim Deb As Boolean
Dim Tb

Application.ScreenUpdating = False
'Initial Datas are in worksheet Sheet1 at columns A & B
With Sheet1
    LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A1:B" & LastLig).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
    Tb = .Range("A2:B" & LastLig + 1)
End With
ReDim Res(1 To 2, 1 To 1)
io = 1
For i = 1 To LastLig - 1
    StrFin = Txt(Tb(i + 1, 1))
    StrIni = Txt(Tb(i, 1))
    If StrFin = StrIni And Num(Tb(i + 1, 1), StrFin) = Num(Tb(i, 1), StrIni) + 1 Then
        If Deb Then io = i
        Deb = False
    Else
        k = k + 1
        ReDim Preserve Res(1 To 2, 1 To k)
        Res(1, k) = Tb(io, 1)
        Res(2, k) = Tb(i, 2)
        Deb = True
        io = i + 1
    End If
Next i
'Result wrote in worksheet Sheet2 at the cell DEST (cf Constant definition)
If k > 0 Then Sheet2.Range(Dest).Resize(k, 2) = Application.Transpose(Res)
End Sub

Private Function Txt(ByVal Str As String) As String
Dim Rg As Object

Set Rg = CreateObject("VBscript.RegExp")
With Rg
    .Pattern = "(\d)(.*)"
    .Global = True
    Txt = .Replace(Str, "")
End With
Set Rg = Nothing
End Function

Private Function Num(ByVal Str As String, Pref As String) As Double

If Str <> "" Then Num = Val(Replace(Str, Pref, ""))
End Function
 

Attachments

  • Sample.xlsm
    46.9 KB · Views: 0
Hi ,

Why do you say that the code is failing for the strings in the cells colored red ? What is it supposed to output for those cells ?

Narayan
 
Hello Narayan, There was a error in the sample data. i corrected it now. Refer Cell from D10 to E13. these could have been merged to 1 range like D2 and E2.
 

Attachments

  • Sample_1.xlsm
    46.9 KB · Views: 0
Hi ,

The problem is that the code was not written to handle a prefix such as G4P ; will your text prefix always be 3 characters long ? If so , we can rewrite the code to remove these 3 characters to get at the number.

Narayan
 
Currently it is true : handling this will solve the purpose for now. However the string can always be dynamic as this contain the serial number of the products. if we can have it like it should always check the rightmost combination of numerical numbers and do the job.
 
Hi ,

The logic can be written in any way ; the point is the logic has to be tailored to the data.

Suppose we want a numeric value from a string of characters , we can do it in 2 ways :

1. Remove all unwanted characters , leaving behind only the wanted characters

2. Extract only the wanted characters

If we use the first kind of logic , we should know which are the unwanted characters ; here again , we can have 2 kinds of logic :

a. The unwanted characters are the left-most 3 characters

b. The unwanted characters are all non-numeric characters

A prefix such as ABC would satisfy either rule ; a prefix such as G4P satisfies only the first rule.

It is the data which is creating a problem.

If the prefix characters are variable in number and type , then we are left with , once more 2 options !

1. We can start from the right and work our way to the left ; will the right-most characters either be numeric or non-numeric ? Or is there a possibility that you can have an input string such as :

G4P0301361G4P

2. If the length of the numeric portion will always be 6 or more digits , we can write the code to count the number of digits , and anything less than 6 can be discarded.

Narayan
 
String specification.
The string will be alphanumeric and there is no certain place for alphabet. the best approach seems trim the desired number of character from right.
Rule 1: G4P0301361AB4T, "N" number of Character (for this 4) not be considered by referring a cell value where i will put how many character not to consider as there may be suffix to string. i will put 0 for no suffix type string.
Rule 2: Start from left and identify the rightmost alphabet. in this case it will be "P". Now the remaining continuous numeric string is 0301361 (this can be of any length)
Rule 3 : Now make the range as the above code do.
Rule 4 : In case it the suffix was there we will add the suffix at last. for example in this case the range would be G4P0301361AB4T to G4P0301XXXAB4T
 

Attachments

  • Sample_Unique_Set.xlsx
    834.3 KB · Views: 0
Last edited:
Hi ,

What you want done can be done , but you should be aware that logically , the following statement is not correct :
Start from left and identify the rightmost alphabet. in this case it will be "P".
The right-most alphabet will be T , the last character of the string G4P0301361AB4T.

Narayan
 
Narayan, Acctuly in rule 1 i asked not to consider the "N" characters at first most and after removing these "N" character the string usable string is G4P0301361AB4T. "N" will be 0 in case while string to be consider for making range in pure numeric numbers like 123455565
Now start from left and the rightmost character is "P" and we get the workable string as G4P0301361AB4T. Now the string is 0301361 to process. now after doing the processing, we will add the suffix and prefix to the results and the final answer whould be G4P0301361AB4T to G4P0301XXXAB4T. Here XXX present the top number we will get in range. Hope it is clear now.

if Rule 1 add a lot of work, then you may please start from Rule 2. i will manually use the left function to get the string which is needed to pass in the code.
 
Hi ,

If at all you are going to specify an additional item of data , then why not specify the number of characters to be ignored from the left ?

Ignoring the suffix is easier than ignoring the prefix , since in the suffix we need to detect the first alpha character. In the prefix , we need to detect the first digit , but this is proving to be a problem because even within the prefix , there can be digits.

Narayan
 
The problem is, the test string can be of any length from 5 Chr to 16 Chr currently and can have any number of alphabet in between. Few Examples

ABC234564
ABC234564P
GJ4563B
G4554433222
G56P444442W
G56P4543222

But it seems that your approach will work.... Let me redefine the statements.

Point 1: We will not not consider last "N" charters from string. N=0 if no character is to be avoided from string. For example if the strings are like G56P444442W, i will put N=1. Now the usable string is G56P444442W
Point 2 :
As you said start moving from right with trimmed string G56P444442 and identify the first alphabet. in this case it is "P" at 4th location. Now you have the actual string to process as 444442.
Point 3 :
Now make the ranges
Point 4 : In case the we had prefix and suffix, we will add those in to the range outcome.

Note : in case the string do not contain any alphabet, whole string will be used to make ranges

regards,
 
if considering the point 1 makes the solution difficult, We can move ahead from point 2 onwards and right part will be taken care mannualy
 
« Code is not handling a certain type of text string. »

Wrong : code just does what is requested !
Code:
Sub Demo1()
S$ = "G4P0301361AB4T":  L& = Len(S)

Do
    If IsNumeric(Mid(S, L, 1)) Then If IsNumeric(Mid(S, L - 1, 1)) Then Exit Do
           L = L - 1
Loop While L > 1

If L > 1 Then
        S = Left(S, L)
    Do
        If Not IsNumeric(Mid(S, L - 1)) Then T$ = Mid$(S, L): Exit Do
               L = L - 1
    Loop While L > 1
End If

MsgBox IIf(T > "", T, "Invalid sequence !")
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Last edited:

So, why it works on my side ?‼ Have you ever tried my demonstration ?!

Better you should crystal clear explain your needs at least
with all possible cases (sources & results with all rules) and
attach a workbook with a source worksheet & a desired result worksheet …
 
Back
Top