xcaliber13

Excel Help

9 posts in this topic

Ok it's Monday Morning and I am just at a complete blank to do this:

I have an excel column that should only have numbers in each cell.  Always a 4 digit number.   But some of the cells get pulled into excel with letters in this column.

Can someone point in the direction I should look to?

Cells look like this:

LLE     2256

N M    2267

M     2298

Need them to look like this:

2256

2267

2298

Basicly delete the letters, keep the numbers.    Just totally blank on how to begin.   Thanks

Share this post


Link to post
Share on other sites



Ok so I have tried this:

For $i = 1 To $iLastRow
    $iCell = _Excel_RangeRead($oWorkbook, Default, "H" & $i)
    If $iCell > "" Then StringRegExpReplace($iCell, "[A-Za-z]", "")

Next

This code does not work.    Is this not a regex problem?     Should I look to do something else? 

Thank you

Share this post


Link to post
Share on other sites

Define "Does not work". How do you know that it doesn't work?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-05-09 - Version 1.2.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

the result is never written back to the excel cell.

 

Share this post


Link to post
Share on other sites

StringRegExpReplace doesn't inherently know what you want to do with the returned value. Have you tried something like:

_Excel_RangeWrite($oWorkbook, Default, StringRegExpReplace($iCell, "[A-Za-z]", ""), "H" & $i)

 


When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

OK!   Thank you everyone for getting me on the right Track.    I got it to work.

This works:

For $i = $iLastRow To 1 Step -1
    $iCell = _Excel_RangeRead($oWorkbook, Default, "H" & $i, 3)
              $sCell = StringRight($iCell, 4)
              _Excel_RangeWrite($oWorkbook, Default, $sCell, "H" & $i)
        Next

Again a big thank you all

Share this post


Link to post
Share on other sites

If performance is an issue then I suggest to use _Excel_RangeRead to read the whole column in one go into an array, modify the array and then rewrite the whole column from the array.
That's much faster!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-05-09 - Version 1.2.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Water,

           Would I then use _ArrayTrim?

Share this post


Link to post
Share on other sites

If you have always to remove the same number of characters from the left then _ArrayTrim could be a solution.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2016-05-09 - Version 1.2.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

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