Read Excel Cell with Text formatting showing Space

[DigDeep]

Hi,

This might be a weird ask but any help is appreciated.

I have an excel which comes from a database. I am trying to read the Cell to see if the word 'State' is present anywhere. Overall my script works all good. But not for this particular Excel. When I looked closely, I found that the text 'State' is written with either a space or a line. Because of the formatting issue, even though I am sating Delete anything else than the text 'State'. It still deletes everything along with this row too.

When I am copying the cell in a notepad, it shows as: "State" and the cursor blinks at the next line.

I tried calling it as 'State', '"State"', 'State ', '"State "' but nothing works.

Can anyone help here to see how we can read this kind of cell correctly? Because this is coming from a database, unfortunately I cannot make any changes there. So the only solution is to see if we can read it here... I am also attaching a sample excel for better understanding.

Local $kCountLines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
    For $k = $kCountLines To 1 Step -1
    Local $Col2 = _Excel_RangeRead($oWorkbook, Default, $LastColName & $k)
        If $Col2 <> 'State' Then
            _Excel_RangeDelete($oWorkbook.ActiveSheet, $k & ":" & $k, 1)
        EndIf
    Next

 

 

 

 

 

 

test.xls

[ViciousXUSMC]

StringinStr()

https://www.autoitscript.com/autoit3/docs/functions/StringInStr.htm

Instead of checking for an exact match, this lets you pretty much "wildcard" your search.

Didn't test this but I think it should work, or atleast give you an idea.

Local $kCountLines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
    
For $k = $kCountLines To 1 Step -1
If Not StringInStr(_Excel_RangeRead($oWorkbook, Default, $LastColName & $k), "State") Then _Excel_RangeDelete($oWorkbook.ActiveSheet, $k & ":" & $k, 1)
Next

 

Edited March 26, 2018 by ViciousXUSMC

[Subz]

I normally find that it as an @LF to the end of the cell, so would normally use StringStripWs($sString, 3), which removes all white space before and after from a string.

[DigDeep]

Thanks @Subz and sorry for the delay. This got fixed by using StringInStr.

Thanks @ViciousXUSMC for the helping hand.