Excel Magic Tricks

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

QUERY in G Sheet
=QUERY(Main_data!A:D,"SELECT A,B,C,D WHERE D='COMPLETE'",1)

=QUERY(Main_data!A:D,"SELECT A,B,C,D WHERE D='INCOMPLETE'",1)

=QUERY(Main_data!A:D,"SELECT A,B,C,D WHERE D='CANCELLED'",1)

=SUMPRODUCT(--ISFORMULA(C3:C17),C3:C17), is used in Excel to calculate the sum of values in a range that are also formulas.

WEEKNUM Function:

=TEXT(A4,"MMM")&", W-"&WEEKNUM(A4)-WEEKNUM(DATE(YEAR(A4),MONTH(A4),1))+1

https://youtu.be/W1kPX3QvdkY

Pop up alert messages in Google Sheets

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.alert(Please do not delete any data!');
}

Upgrade from VLOOKUP in Excel to INDEX MATCH for greater power and flexibility.