Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas

127 Formulas

11 Categories

3 Skill levels

Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Chapter 11

Text & Reshaping (365)

115 TEXTSPLIT =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) Splits text strings by using column and row delimiters. The split fragments spill automatically into adjacent cells. Text & Reshaping (365) Beginner Office 365
Syntax
```
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
```
Arguments

text

The text you want to split.

col_delimiter

The text that marks the point where to split text across columns.

[row_delimiter]

Optional delimiter to split text across rows instead of columns.
Sample
```
=TEXTSPLIT(A2, ", ")
```
Result Excel | PowerBI | SQL (Spilled)
Sample data
Download

A B C D
2 Excel, PowerBI, SQL Excel PowerBI SQL
3 Python, Pandas, AI Python Pandas AI
4 Sales, Marketing, HR Sales Marketing HR

Pro tip
You can pass multiple delimiters as an array constant, e.g. =TEXTSPLIT(A2, {",", ";", "|"}).

Common pitfall
Ensure surrounding destination cells are completely empty to avoid a #SPILL! error.
116 TEXTBEFORE =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) Returns text that occurs before a given delimiter. Eliminates the traditional nested combinations of LEFT, FIND, and LEN. Text & Reshaping (365) Beginner Office 365
Syntax
```
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
```
Arguments

text

The text you are searching within.

delimiter

The character that marks the point before which you want to extract.

[instance_num]

The instance of the delimiter (default is 1). Negative numbers search from the end.
Sample
```
=TEXTBEFORE(A2, "@")
```
Result rohan.sharma
Sample data
Download

A B C D
2 rohan.sharma@corp.com rohan.sharma corp.com Corp
3 priya.singh@corp.com priya.singh corp.com Corp
4 amit.verma@corp.com amit.verma corp.com Corp

Pro tip
Use a negative instance number (e.g. -1) to extract everything before the LAST occurrence of a character, such as folder file paths.

Common pitfall
If delimiter is not found and if_not_found is not supplied, Excel returns #N/A.
117 TEXTAFTER =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) Returns text that occurs after a given character or delimiter substring within a cell. Text & Reshaping (365) Beginner Office 365
Syntax
```
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
```
Arguments

text

The text from which to extract.

delimiter

The character that marks the extraction start point.

[instance_num]

The instance number of the delimiter. Default is 1.
Sample
```
=TEXTAFTER(A2, "-")
```
Result 89041
Sample data
Download

A B C D
2 INV-89041 89041 INV Delhi
3 INV-89042 89042 INV Mumbai
4 ORD-45129 45129 ORD Bangalore

Pro tip
To extract the file extension from Annual_Report_2025.final.pdf, use =TEXTAFTER(A1, ".", -1) to get "pdf".

Common pitfall
Case sensitivity matters unless match_mode is set to 1 (case-insensitive).
118 VSTACK =VSTACK(array1, [array2], ...) Appends arrays vertically (row beneath row) to combine multiple separate tables, monthly reports, or sheet ranges into one master table. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=VSTACK(array1, [array2], ...)
```
Arguments

array1, [array2, ...]

The arrays or cell ranges to append vertically.
Sample
```
=VSTACK(A2:B3, A5:B6)
```
Result Combined Table (Spills)
Sample data
Download

Pro tip
Wrap in FILTER to exclude blank rows from the combined tables: =FILTER(VSTACK(Sheet1:Sheet4!A2:D50), VSTACK(Sheet1:Sheet4!A2:A50)"").

Common pitfall
If arrays have different column counts, VSTACK pads the missing columns with #N/A errors.
119 HSTACK =HSTACK(array1, [array2], ...) Appends arrays horizontally (column next to column) to combine data side-by-side without needing manual copy-pasting or complex formulas. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=HSTACK(array1, [array2], ...)
```
Arguments

array1, [array2, ...]

The arrays or ranges to append horizontally.
Sample
```
=HSTACK(A2:A5, D2:D5)
```
Result Rohan Sharma | ?85,000
Sample data
Download

A B C D E
2 Rohan Sharma Tech Rohan Sharma ?85,000 85,000
3 Priya Singh Sales Priya Singh ?92,000 92,000
4 Amit Verma HR Amit Verma ?64,000 64,000

Pro tip
Use HSTACK with SEQUENCE to auto-attach clean row numbers: =HSTACK(SEQUENCE(ROWS(A2:B10)), A2:B10).

Common pitfall
If ranges have different row counts, shorter ranges are padded with #N/A in the extra rows.
120 TOCOL =TOCOL(array, [ignore], [scan_by_column]) Transforms a two-dimensional grid or matrix of cells into a single vertical column. Can automatically ignore blank cells and errors. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=TOCOL(array, [ignore], [scan_by_column])
```
Arguments

array

The 2D array or range to flatten.

[ignore]

0 (keep all - default), 1 (ignore blanks), 2 (ignore errors), 3 (ignore blanks and errors).

[scan_by_column]

FALSE (default) scans row-by-row; TRUE scans column-by-column.
Sample
```
=TOCOL(A2:B4, 1)
```
Result Desk 1 (Spills)
Sample data
Download

A B C D
2 Desk 1 Desk 4 Active Desk 1
3 Desk 2 Active Desk 4
4 Desk 3 Desk 5 Active Desk 2

Pro tip
Set the second argument ignore to 1 to automatically filter out all empty cells in the matrix!

Common pitfall
By default, it reads row-by-row. If you need column-by-column order, set the 3rd argument scan_by_column to TRUE.
121 TOROW =TOROW(array, [ignore], [scan_by_column]) Transforms a two-dimensional grid or column into a single continuous horizontal row, with options to strip blanks and errors. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=TOROW(array, [ignore], [scan_by_column])
```
Arguments

array

The range or array to turn into a row.

[ignore]

0 (keep all), 1 (ignore blanks), 2 (ignore errors), 3 (ignore blanks and errors).
Sample
```
=TOROW(A2:A5)
```
Result Rohan (Spills across)
Sample data
Download

A B C D
2 Rohan Tech Delhi 5
3 Priya Sales Mumbai 4
4 Amit HR Bangalore 5

Pro tip
Combine with TEXTJOIN if you need to merge an entire matrix into a single sentence or comma-delimited string.

Common pitfall
Ensure enough empty columns exist to the right of the active cell to avoid #SPILL! errors.
122 WRAPROWS =WRAPROWS(vector, wrap_count, [pad_with]) Wraps the provided row or column vector into a 2D array by rows after reaching a specified number of elements per row. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=WRAPROWS(vector, wrap_count, [pad_with])
```
Arguments

vector

The 1-dimensional row or column to wrap.

wrap_count

The maximum number of values for each row.

[pad_with]

Value with which to pad missing cells if vector does not divide evenly.

Sample

Result Item 1 | Item 2

Sample data
Download

A B C D
2 Item 1 Item 1 Item 2
3 Item 2 Item 3 Item 4
4 Item 3 Item 5 Item 6

Common pitfall
wrap_count must be a positive integer greater than 0; otherwise, Excel errors with #VALUE!.
123 WRAPCOLS =WRAPCOLS(vector, wrap_count, [pad_with]) Wraps the provided vector into a 2D array by columns after reaching a specified number of values per column. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=WRAPCOLS(vector, wrap_count, [pad_with])
```
Arguments

vector

The 1D vector to wrap.

wrap_count

The maximum number of values for each column.

[pad_with]

The value to pad with if items do not divide evenly.
Sample
```
=WRAPCOLS(A2:A7, 3, "")
```
Result Batch 1 (Spills 3x2)
Sample data
Download

A B C D
2 Batch 1 Batch 1 Batch 4 Active
3 Batch 2 Batch 2 Batch 5 Active
4 Batch 3 Batch 3 Batch 6 Active

Pro tip
Use WRAPCOLS when creating newspaper-style multi-column vertical reading layouts.

Common pitfall
Vector must be a single row or single column range; passing a 2D range produces #VALUE!.
124 DROP =DROP(array, rows, [columns]) Excludes a specified number of rows or columns from the start or end of an array. Positive numbers drop from top/left; negative numbers drop from bottom/right. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=DROP(array, rows, [columns])
```
Arguments

array

The array from which to drop rows or columns.

rows

The number of rows to drop. Positive drops from top; negative drops from bottom.

[columns]

The number of columns to drop. Positive drops from left; negative drops from right.
Sample
```
=DROP(A2:B6, 1)
```
Result Priya (Header row dropped)
Sample data
Download

Pro tip
Combine with TAKE: =TAKE(DROP(A2:D50, 10), 10) to paginate records 11 through 20 seamlessly!

Common pitfall
Dropping as many (or more) rows/columns than exist in the array returns a #CALC! error (empty array).
125 TAKE =TAKE(array, rows, [columns]) Returns a specified number of contiguous rows or columns from the start or end of an array. Positive numbers take from top/left; negative numbers take from bottom/right. Text & Reshaping (365) Beginner Office 365
Syntax
```
=TAKE(array, rows, [columns])
```
Arguments

array

The array from which to take rows or columns.

rows

Number of rows to take. Positive takes from beginning; negative takes from end.

[columns]

Number of columns to take.
Sample
```
=TAKE(A2:B6, 3)
```
Result Top 3 (Spills)
Sample data
Download

Pro tip
Use negative rows, e.g. =TAKE(A2:C50, -1), to instantly extract the final row of a table without knowing its total row count!

Common pitfall
Supplying 0 for rows or columns produces a #CALC! error.
126 CHOOSECOLS =CHOOSECOLS(array, col_num1, [col_num2], ...) Returns the specified columns from an array in the exact order you request. Allows creating custom views without hiding or reordering original columns. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=CHOOSECOLS(array, col_num1, [col_num2], ...)
```
Arguments

array

The array containing the columns to be returned.

col_num1, [col_num2, ...]

Column index numbers to extract. Order dictates the new output column sequence.
Sample
```
=CHOOSECOLS(A2:D5, 1, 4)
```
Result Rohan | High
Sample data
Download

A B C D E
2 Rohan Sharma X-901 Rohan Sharma High High
3 Priya Singh X-902 Priya Singh Top Top
4 Amit Verma X-903 Amit Verma Medium Medium

Pro tip
Negative indices count backwards from the right! CHOOSECOLS(A:Z, -1) always returns the last column (Z).
127 CHOOSEROWS =CHOOSEROWS(array, row_num1, [row_num2], ...) Returns specified rows from an array in the order specified. Ideal for isolating milestone rows, odd rows, or custom sample rows. Text & Reshaping (365) Intermediate Office 365
Syntax
```
=CHOOSEROWS(array, row_num1, [row_num2], ...)
```
Arguments

array

The array containing the rows to be returned.

row_num1, [row_num2, ...]

Row index numbers to extract.
Sample
```
=CHOOSEROWS(A2:B6, 1, 3, 5)
```
Result Step 1 (Spills)
Sample data
Download

Pro tip
Use negative row numbers to reference from the bottom: CHOOSEROWS(data, -1, -2) returns the last two rows reversed!

Common pitfall
Passing row numbers greater than the total number of rows in the array produces a #VALUE! error.

	A	B	C	D
2	Excel, PowerBI, SQL	Excel	PowerBI	SQL
3	Python, Pandas, AI	Python	Pandas	AI
4	Sales, Marketing, HR	Sales	Marketing	HR

	A	B	C	D
2	rohan.sharma@corp.com	rohan.sharma	corp.com	Corp
3	priya.singh@corp.com	priya.singh	corp.com	Corp
4	amit.verma@corp.com	amit.verma	corp.com	Corp

	A	B	C	D
2	INV-89041	89041	INV	Delhi
3	INV-89042	89042	INV	Mumbai
4	ORD-45129	45129	ORD	Bangalore

	A	B	C	D	E
2	Rohan Sharma	Tech	Rohan Sharma	?85,000	85,000
3	Priya Singh	Sales	Priya Singh	?92,000	92,000
4	Amit Verma	HR	Amit Verma	?64,000	64,000

	A	B	C	D
2	Desk 1	Desk 4	Active	Desk 1
3	Desk 2		Active	Desk 4
4	Desk 3	Desk 5	Active	Desk 2

	A	B	C	D
2	Batch 1	Batch 1	Batch 4	Active
3	Batch 2	Batch 2	Batch 5	Active
4	Batch 3	Batch 3	Batch 6	Active

	A	B	C	D	E
2	Rohan Sharma	X-901	Rohan Sharma	High	High
3	Priya Singh	X-902	Priya Singh	Top	Top
4	Amit Verma	X-903	Amit Verma	Medium	Medium

	A	C	D
2	Item 1	Item 1	Item 2
3	Item 2	Item 3	Item 4
4	Item 3	Item 5	Item 6