Excel Magic Tricks

How to Hide Important Excel Reports Using a Passkey

Excel Ctrl + G Shortcut | Quickly Select & Find Data in Excel

✅ How to use Ctrl + G
✅ How to quickly jump to a specific cell or range
✅ How to select specific data efficiently

*Doughnut charts* are excellent for dashboards in Google Sheets, as they clearly show the proportional contribution of each category to the overall total.

Magic!
Ctrl + H

Time Group Formula in Excel.

=TEXT(D2,"h AM/PM") & " to " & TEXT(D2+1/24,"h AM/PM")

Time Group Formula in Excel.

=TEXT(D2,"h AM/PM") & " to " & TEXT(D2+1/24,"h AM/PM")

Time Group:
7 AM – 8 AM
8 AM – 9 AM
9 AM – 10 AM

How to solve an extremely large number?

YEARFRAC function

It's about to solve the Microsoft Excel related problem very easily.

How to separate numbers in column A from alphabet characters and output them in column B using VBA in Excel.
Code:

Sub ExtractNumbersFromString()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim regex As Object
Dim matches As Object
Dim match As Object

' Set the worksheet to the one you want to work with
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

' Create a regular expression object
Set regex = CreateObject("VBScript.RegExp")
regex.Global = True
regex.IgnoreCase = True
regex.Pattern = "\d+" ' Match one or more digits

' Find the last used row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each row in column A
For i = 1 To lastRow
' Check if the cell contains a string
If Not IsEmpty(ws.Cells(i, "A").Value) And IsStringNumeric(ws.Cells(i, "A").Value) Then
' Extract numerical values from the string using regular expression
Set matches = regex.Execute(ws.Cells(i, "A").Value)
' Concatenate all matched numbers into a string separated by space
For Each match In matches
ws.Cells(i, "B").Value = ws.Cells(i, "B").Value & " " & match.Value
Next match
' Trim leading space
ws.Cells(i, "B").Value = Trim(ws.Cells(i, "B").Value)
Else
' If it's not a string or doesn't contain any numbers, leave the corresponding cell in column B empty
ws.Cells(i, "B").Value = ""
End If
Next i
End Sub

Function IsStringNumeric(str As String) As Boolean
' Check if a string contains at least one numerical character
Dim i As Integer
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
IsStringNumeric = True
Exit Function
End If
Next i
IsStringNumeric = False
End Function