Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Multiple lines in cell using functions CHAR
  • Week number to Month number DATE
  • Links between two workbooks – dynamically using functions INDIRECT
  • MID + SEARCH to convert cell to rows MID
  • When Excel functions are not enough Excel User tips
  • Text-to-Columns, dynamically using formulas IFERROR
  • Get 3rd Wednesday of month Basic Math
  • Skills Grid Conditional Formatting

Category: SEARCH

Middle name + 1st name

Posted on February 3, 2024 By ANmar
Middle name + 1st name

Get the initial (middle name) from a triple name in a cell.
C4 The cell contains the triple name separated with spaces.
=LEFT(C4,1) & "." & MID(C4, SEARCH(" ", C4, 1) + 1, 1) & "." & MID(C4, SEARCH(" ", C4, SEARCH(" ", C4, 1) + 1) + 1, 1) & "."

Read More “Middle name + 1st name” »

Basic Math, Formulas - combined functions, LEFT, MID, SEARCH, Standard functions, Texts and Strings

Cuts string of list of items

Posted on August 17, 2022 By ANmar
Cuts string of list of items

An old lesson to one of my early classes to show how can we cut a string having list of items into its parts. (At least 1st three parts) using formulas only.

Read More “Cuts string of list of items” »

Formulas - combined functions, LEFT, MID, SEARCH, Standard functions, Texts and Strings

Always get 1st name (or last)

Posted on July 23, 2022 By ANmar
Always get 1st name (or last)

Formula to get first name from 2 formats either (Lastname, First) or (Firstname Last) formats.

Read More “Always get 1st name (or last)” »

Formulas - combined functions, IF, ISERROR, LEFT, Len, Logical, RIGHT, SEARCH, Standard functions, Texts and Strings, TRIM

Worksheet name, dynamically using formula

Posted on April 3, 2018 By ANmar
Worksheet name, dynamically using formula

Get the name of the active worksheet in active workbook, using formulas only

This function needs to have workbook saved

Then, you just paste below formula into any cell …

Read More “Worksheet name, dynamically using formula” »

CELL, MID, SEARCH

Text-to-Columns, dynamically using formulas

Posted on April 3, 2018 By ANmar
Text-to-Columns, dynamically using formulas

I often use the technique of concatenating columns into 1 cell with separators.

Something like the CSV, 1 line that has all values for a single row (all columns for that row) into 1 text block.

And then, because of that, I need to extract that back, into table

Read More “Text-to-Columns, dynamically using formulas” »

IFERROR, Len, MID, SEARCH

Extract current (Active) workbook name

Posted on September 14, 2015 By ANmar
Extract current (Active) workbook name

I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is….   And if we want to go 1 step further, I often have the version of the tool as part of the filename,…

Read More “Extract current (Active) workbook name” »

CELL, Formulas - combined functions, Information, Lookup and References, MID, SEARCH, Standard functions, Texts and Strings

Get column name (columnname as A,B,C, etc) as input inside cell

Posted on April 6, 2015 By ANmar
Get column name (columnname as A,B,C, etc) as input inside cell

Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters

4-6-2015 9-25-15 AM

However, the COLUMN() is kind of tricky

Because of the fact that cell addresses (or references) are based on the old style of R1C1

=Column()

Read More “Get column name (columnname as A,B,C, etc) as input inside cell” »

ADDRESS, CHAR, COLUMN, Formulas - combined functions, LEFT, Lookup and References, ROW, SEARCH, Standard functions, Texts and Strings

Extract Address into table

Posted on February 10, 2012 By ANmar No Comments on Extract Address into table
Extract Address into table

Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table

Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above

Which is usually what you got from any list of addresses online, and you want to convert it to table.

This is exactly what one of clients had and needed, so here is the file that does that.

Basically you need to have 5 columns with formula in column G as:

=OFFSET($B$1,(ROW()-5)*4+4,0)

to extract first line (Starting from cell B5

Then in column H as:

Read More “Extract Address into table” »

Formulas - combined functions, LEFT, Lookup and References, MID, OFFSET, ROW, SEARCH, Standard functions, Texts and Strings

MID + SEARCH to convert cell to rows

Posted on January 23, 2012 By ANmar No Comments on MID + SEARCH to convert cell to rows
MID + SEARCH to convert cell to rows

I have been there and done that more than once before This time I had a really large cell, more than 3k characters need to be converted into rows, and here is how you can do it. This one uses MID with SEARCH to do that Use one column to get the character number of…

Read More “MID + SEARCH to convert cell to rows” »

MID, SEARCH, Standard functions, STRING, Texts and Strings

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Multiple-Validations (connected to each other) Data Validation
  • CONVERT function CONVERT
  • Multiple Dual-Validations COUNTA
  • Search across sheets in function ADDRESS
  • Insert Blank rows into table using functions CHAR
  • Walking Columns ActiveX controls
  • CamelCase to Camel Case CHAR
  • Sorting with functions only CHOOSE

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme