Jump to content
DigDeep

Read Excel Cell with Text formatting showing Space

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 by ViciousXUSMC

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
DigDeep

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

Thanks @ViciousXUSMC for the helping hand.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.