Highlight Cells with Misspelled Words
Excel doesn’t have a spell check as it has in Word or PowerPoint. While you can run the spell check by hitting the F7 key, there is no visual cue when there is a spelling mistake.
Use this code to instantly highlight all the cells that have a spelling mistake in it.
'This code will highlight the cells that have misspelled words Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub
Note: that the cells that are highlighted are those that have text that Excel considers as a spelling error. In many cases, it would also highlight names or brand terms that it doesn’t understand.
How to Get Only the Numeric Part from a String in Excel
If you want to extract only the numeric part or only the text part from a string, you can create a custom function in VBA.
You can then use this VBA function in the worksheet (just like regular Excel functions) and it will extract only the numeric or text part from the string.
Something as shown below:
Below is the VBA code that will create a function to extract numeric part from a string:
'This VBA code will create a function to get the numeric part from a string Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function
You need place in code in a module, and then you can use the function =GetNumeric in the worksheet.
This function will take only one argument, which is the cell reference of the cell from which you want to get the numeric part.
Similarly, below is the function that will get you only the text part from a string in Excel:
'This VBA code will create a function to get the text part from a string Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function
So these are some of the useful Excel macro codes that you can use in your day-to-day work to automate tasks and be a lot more productive.
No comments:
Post a Comment