Check length of digits only in alphanumeric string

[drmingle]

If I have the following in A1:

"DRG"

and have the following in A2:

<TABLE style="WIDTH: 1109pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1478><COLGROUP><COL style="WIDTH: 1109pt; mso-width-source: userset; mso-width-alt: 54052" width=1478><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 1109pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 width=1478>[FONT=Times New Roman]encounterRow.encounterType.code in ('I','IB') and encounterRow.msDRG1.code = '163'[/FONT]</TD></TR></TBODY></TABLE>

I want to be able to use an if than statement that only tests the numerical data in the alphanumeric string.

For example, I would like to be sure the number in the alphanumeric string is equal to 3 digits, I want to return false for anything <> 3.

I thought I could use some form of Len(A2)<>3, but I need to be able to target more specifically a subpart of the cell instead of the entire cell (namely the numbers only).

NOTE: I am using Excel 2007.

 

[Scott Huish]

If it can be assumed that your data will always follow that format: The number follows an = sign in quotes at the end of the string, and that it is always a number in the quotes, then, this should suffice:

=LEN(TRIM(REPLACE(A2,1,FIND("=",A2),"")))=5

 

[Snakehips]

drmingle,

Also assuming that your number is in the last 4 characters, this should ensure three numeric characters .......

Sheet4

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 279px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>sgdfgetgdf.hdto = '16A'</TD><TD>No</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>sgdfgetgdf.hdto = '63'</TD><TD>No</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>sgdfgetgdf.hdto = '163'</TD><TD>Yes</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IFERROR(IF(VALUE(LEFT(RIGHT(A1,4),3))>99,"Yes",),"No")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Scott Huish]

That would have a problem with this:

sgdfgetgdf.hdto = '1634'

 

[Snakehips]

HOTPEPPER,

Well spotted.

2/10 for trying??

 

[drmingle]

Thanks for the solution...

In some cases I am running into the following:

<TABLE style="WIDTH: 1109pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1478><COLGROUP><COL style="WIDTH: 1109pt; mso-width-source: userset; mso-width-alt: 54052" width=1478><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 1109pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_3044482 class=xl65 height=17 width=1478>[FONT=Times New Roman]encounterRow.encounterType.code in ('I', 'IB') and encounterRow.msDRG1.code between ('619','621') and ( encounterRow.principalProcedures.icd9cmCodeObj.code in ('4431','4438','4439','445','4468','4469','4495','4496','4497','4498','4499') or encounterRow.pr[/FONT]</TD></TR></TBODY></TABLE>

Here I have multiple items msDRG1.code between ('619','621') that I need to check and it doesn't follow the previous format of "=". The other complication is another number set icd9cmCodeObj.code in ('4431','4438','4439','445','4468','4469','4495','4496','4497','4498','4499') which follows another number format.

 

[drmingle]

I was able to find a solution here using VBA:
http://www.ozgrid.com/VBA/ExtractNum.htm

However

if I have this in a cell:

<TABLE style="WIDTH: 208pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=277><COLGROUP><COL style="WIDTH: 208pt; mso-width-source: userset; mso-width-alt: 10130" width=277><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 208pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=20 width=277>

WEMSDVG ABG 223 (EN)

</TD></TR></TBODY></TABLE>

I get this result 223, which is what I want

but in a cell like this

<TABLE style="WIDTH: 1343pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1790><COLGROUP><COL style="WIDTH: 1343pt; mso-width-source: userset; mso-width-alt: 65462" width=1790><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 1343pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=20 width=1790>encounterRow.encounterType.code in ('I','IB') and encounterRow.msDRG1.code = '223'</TD></TR></TBODY></TABLE>

I get this result 1223, which is not what I want

Any idea why this code can see the number in one cell and not the other...

Below is the function being used:

Function ExtractNumber(rCell As Range, _
     Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
 
Dim iCount As Integer, i As Integer, iLoop As Integer
    Dim sText As String, strNeg As String, strDec As String
    Dim lNum As String
    Dim vVal, vVal2
 
     ''''''''''''''''''''''''''''''''''''''''''
     'Written by OzGrid Business Applications
     'www.ozgrid.com
 
     'Extracts a number from a cell containing text and numbers.
     ''''''''''''''''''''''''''''''''''''''''''
    sText = rCell
    If Take_decimal = True And Take_negative = True Then
        strNeg = "-" 'Negative Sign MUST be before 1st number.
        strDec = "."
    ElseIf Take_decimal = True And Take_negative = False Then
        strNeg = vbNullString
        strDec = "."
    ElseIf Take_decimal = False And Take_negative = True Then
        strNeg = "-"
        strDec = vbNullString
    End If
    iLoop = Len(sText)
 
            For iCount = iLoop To 1 Step -1
            vVal = Mid(sText, iCount, 1)
 
 
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
                    i = i + 1
                    lNum = Mid(sText, iCount, 1) & lNum
                        If IsNumeric(lNum) Then
                            If CDbl(lNum) < 0 Then Exit For
                        Else
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
                        End If
                End If
 
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
            Next iCount
 
 
    ExtractNumber = CDbl(lNum)
 
End Function

 

[Snakehips]

drmingle,

The problem is that it is finding the '1' in

encounterRow.encounterType.code in ('I','IB') and encounterRow.msDRG[COLOR="Red"]1[/COLOR].code = '223'

and treating that as part of the number you are trying to extract.

Whether you can combat that sort of thing really depends upon being able to define All such possibilities. Eg. If it is the case that the only instance of an 'extra' number is when DRG is part of the string and the extra is a single digit you could remove the resultant left digit.
If there are too many oddball strings you may find it difficult to catch all possibilities.

Hope that helps.

 

[drmingle]

I should have seen that...

...thanks Tony.

 

[Snakehips]

drmingle,

Does this sort of approach offer you a work-around?

Modified Function sets 3 characters, in the function test string, following DRG to X so as to exclude up to 3 unwanted digits from the extracted result.

Function ExtractNumber(rCell As Range, _
     Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
 
Dim iCount As Integer, i As Integer, iLoop As Integer
    Dim sText As String, strNeg As String, strDec As String
    Dim lNum As String
    Dim vVal, vVal2
 
     ''''''''''''''''''''''''''''''''''''''''''
     'Written by OzGrid Business Applications
     'www.ozgrid.com
 
     'Extracts a number from a cell containing text and numbers.
     ''''''''''''''''''''''''''''''''''''''''''
    sText = rCell
 
   [COLOR=blue]On Error Resume Next[/COLOR]
[COLOR=blue]   With Application.WorksheetFunction[/COLOR]
[COLOR=blue]  sText = .Replace(sText, .Find("DRG", sText, 1), 6, "xxx")    [/COLOR]
[COLOR=blue]   End With[/COLOR]
[COLOR=blue]   On Error GoTo 0[/COLOR]
 
    If Take_decimal = True And Take_negative = True Then
        strNeg = "-" 'Negative Sign MUST be before 1st number.
        strDec = "."
    ElseIf Take_decimal = True And Take_negative = False Then
        strNeg = vbNullString
        strDec = "."
    ElseIf Take_decimal = False And Take_negative = True Then
        strNeg = "-"
        strDec = vbNullString
    End If
    iLoop = Len(sText)
 
            For iCount = iLoop To 1 Step -1
            vVal = Mid(sText, iCount, 1)
 
 
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
                    i = i + 1
                    lNum = Mid(sText, iCount, 1) & lNum
                        If IsNumeric(lNum) Then
                            If CDbl(lNum) < 0 Then Exit For
                        Else
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
                        End If
                End If
 
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
            Next iCount
 
 
    ExtractNumber = CDbl(lNum)
 
End Function