alphanumeric

[Ralter649]

I am tring to delete alphanumerics that are < or > a given amount in a columb. An example of the values i am going workingwith are: (1 letter/ 4 numbers/2 letters) s1001pg, s1022pl, s1042hd, ect.

I can not figure out how to delete anything > then ex: s1022pl

 

[hiker95]

Ralter649,

Please see reply #2, by Peter_SSs, MrExcel MVP, Moderator, at the next link, to give us a screenshot of the raw data in column B, and, a screenshot of what the results (manually formatted by you) should look like.

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[FDibbins]

Any time you have anything other than numbers in a cell, that cell becomes text. So you are asking excel to decide if Tuesday is < or > cat. You need to break out the numbers, then you can try what you want to do

 

[jolivanes]

There are several solutions here.
https://www.mrexcel.com/forum/excel-questions/362461-split-cell-into-alpha-numerics.html#post1794525

 

[Dim Me as xlNoob]

Just another thought on how to do it converting individual characters into ascii and comparing that way.

Sub AlphaCompare()
    Dim strMin As String, strMax As String
    Dim cell As Range, rng As Range
    Dim strLimit As String
    
    Set rng = Range("A1:A50") ' Your range of values you want to parse
    strMin = Range("B1").Value ' Just need to adjust ranges here to suit where your data and constraints are
    strMax = Range("B2").Value
    If strMin = "" And strMax <> "" Then GoTo UpperLimit
    If strMax = "" And strMin <> "" Then GoTo LowerLimit
    If strMin <> "" And strMax <> "" Then strLimit = "Ranged": GoTo UpperLimit
    If strMin = "" And strMax = "" Then End
    
UpperLimit:
    For Each cell In rng
        If Len(strMax) > Len(cell.Value) Or cell.Value = "" Then GoTo nextUpper
        If Len(strMax) < Len(cell.Value) Then cell.Clear: GoTo nextUpper
        For i = 1 To Len(cell.Value)
            If Asc(LCase(Mid(cell.Value, i, 1))) > Asc(LCase(Mid(strMax, i, 1))) Then cell.Clear: GoTo nextUpper
            If Asc(LCase(Mid(cell.Value, i, 1))) < Asc(LCase(Mid(strMax, i, 1))) Then GoTo nextUpper
        Next i
nextUpper:
    Next cell
    If strLimit = "Ranged" Then GoTo LowerLimit
    End
    
LowerLimit:
    For Each cell In rng
        If Len(strMin) < Len(cell.Value) Or cell.Value = "" Then GoTo nextLower
        If Len(strMin) > Len(cell.Value) Then cell.Clear: GoTo nextLower
        For i = 1 To Len(cell.Value)
            If Asc(LCase(Mid(cell.Value, i, 1))) < Asc(LCase(Mid(strMin, i, 1))) Then cell.Clear: GoTo nextLower
            If Asc(LCase(Mid(cell.Value, i, 1))) > Asc(LCase(Mid(strMin, i, 1))) Then GoTo nextLower
        Next i
nextLower:
    Next cell
    End
End Sub