Mike P

Posted on Aug 8, 2021

Excel Tricks

#productivity #tutorial

My commonly used Excel and Google Sheets formulas and tricks

Here’s the GitHub:
https://github.com/mikeprivette/exceltricks

Content

Excel Tricks

Time and Date Formulas

Convert the format "Thu Oct 02 12:03:39 GMT 2014" to "10/02/2014"

=CONCATENATE("10/",MID(A2,9,2),"/2014")

Convert the format "2014-Dec-01 5:00:54 AM" to "12/01/2014"

Perform a Text-to-Columns on the cells to split the date from the time information (assuming you don't need time)
You will be left with this:

 |__A1__|  |__B1__|
 2014-Dec-01  05:00:54 AM

On cell A1 rearrange the text and add in the date delimiters:

=CONCATENATE(MID(A2,6,3)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4))

Result = Dec/01/2014

Do a Find & Replace "Dec" with "12"
Cells get automatically converted to Date/Time format
Repeat for different months

Convert EPOCH format (Unix time) to Gregorian format (mm/dd/yyyy hh:mm:ss)

Unix time is the number of seconds since January 1, 1970.

=CELL/(60*60*24)+"1/1/1970"

Turns 1424783916.796051000 = 02/24/2015 13:18:37

Convert a date and time field to ISO 8601 timestamp format

Example: 8/3/21 12:12:12 PM to 2021-08-03T12:12:12

=TEXT(A1,"yyyy-mm-ddThh:MM:ss")

Convert a ISO 8601 timestamp format field to date and time

Example: 2021-08-03T12:12:12 to 8/3/21 12:12:12 PM

=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

Number Manipulation

Convert $20,000,000.00 to $20.0M

Select the cell you want to convert and add the following custom number format

$[>=999950]0.0,,"M";[<=-999950]0.0,,"M";0.0,"K"

Text Manipulation

Find what is to the RIGHT of the last instances of a specific character

Example = Drive:\Folder\SubFolder\Filename.ext (where you just want to find Filename.ext)

Find to the right of the last "\" character

=REGEXEXTRACT(A1,"\\([^\\]*$)")

To find what's to the LEFT, just replace "RIGHT" with "LEFT" in the formula

Example = "First_Name Last_Name" (where you just want "First_Name")

=REGEXEXTRACT(A1,"(^[^ ]*) ")

Find if cell contains a space

=IF(COUNTIF(H2,"* *"),"No","Yes")

Extract text between two characters in a cell

=REGEXEXTRACT(A1,"vip\.ce\.(.*)\.http")

Original = vip.ce.api-prd.website.com.http

After = api-prd.website.com

Trim All Whitespace Including Nonbreaking Space Characters (nbsp)

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))

VLookUp and Replace #N/A with some text

=IF(ISNA(VLOOKUP(A2,<Table Range>,1,FALSE)),"Thing not found",VLOOKUP(A2,<Table Range>,1,FALSE))

Search for text within a cell and label it as X

=IF(IFERROR(SEARCH("<word>",A2),0),"Cleaned",IF(IFERROR(SEARCH("<other word>",A2),0),"Unknown","Not Cleaned"))

Lookup a Value in 2 Different Columns and return the one you want

=Index(array, Match(value_to_lookup, lookup_array, match_type))

=INDEX('TabName'!$A$1:$C$1000, MATCH('TabName'!A2,'TabName'!$A$1:$C$1000,0))

Get OS Short name from long Operating System name (Windows 10 Enterprise = Windows)

=IF(IFERROR(SEARCH("Windows",C2),0),"Windows",IF(IFERROR(SEARCH("AIX",C2),0),"AIX",IF(IFERROR(SEARCH("Linux",C2),0),"Linux",IF(IFERROR(SEARCH("SunOS",C2),0),"SunOS",IF(IFERROR(SEARCH("OS X",C2),0),"Mac","Unknown")))))

Get system type from OS (Windows Serer 2012 = Server)

=IF(IFERROR(SEARCH("Server",E2),0),"Server",IF(IFERROR(SEARCH("AIX",E2),0),"Server",IF(IFERROR(SEARCH("Linux",E2),0),"Server",IF(IFERROR(SEARCH("SunOS",E2),0),"Server",IF(IFERROR(SEARCH("Enterprise",E2),0),"Desktop",IF(IFERROR(SEARCH("Pro",E2),0),"Desktop",IF(IFERROR(SEARCH("Embedded",E2),0),"Desktop",IF(IFERROR(SEARCH("Windows 7",E2),0),"Desktop",IF(IFERROR(SEARCH("Windows 10",E2),0),"Desktop",IF(IFERROR(SEARCH("OS X",E2),0),"Desktop","Unknown"))))))))))

DEV Community