Jump to content
xcaliber13

Excel Help

Recommended Posts

xcaliber13

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
xcaliber13

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
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
AutoBert

 

the result is never written back to the excel cell.

 

Share this post


Link to post
Share on other sites
JLogan3o13

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)

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
xcaliber13

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

Water,

           Would I then use _ArrayTrim?

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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

×