I recently got access to the new =COPILOT() worksheet function in Microsoft 365 and spent some time trying it on everyday spreadsheet tasks.

Here are a few examples that I thought were genuinely useful.

1. Generate sample data

You can ask Copilot to generate things like:

• Fictional project names
• Employee job titles
• Product descriptions
• Customer comments

Example:

=COPILOT("Generate 10 fictional project names")

Possible result:

Project Name
Project Aurora
Green Horizon
Northstar Initiative
BluePeak
Atlas Connect
NovaWorks
Summit Path
BrightBridge
Vertex One
Clearview Project

2. Categorize text automatically

Suppose column A contains support tickets.

Support ticket
Can't sign in to Microsoft 365
Outlook won't send emails
Printer is offline
Excel crashes when opening a large file
Forgot my Windows password
Outlook is running very slowly

In the next column, ask Copilot to categorize each ticket.

Example prompt:

=COPILOT("Assign a category to each support ticket", A2:A7)

Possible result:

Category
Account
Email
Hardware
Software
Account
Email

3. Estimate priority

If you have a long list of requests or issues, Copilot can suggest which ones should be reviewed first.

Example:

=COPILOT("Rate each request as High, Medium, or Low priority", A2:A20)

Obviously you'd still review the results, but it's a nice starting point.

4. Extract information from messy text

Suppose one cell contains:

John Smith
Senior Engineer
john@contoso.com

Instead of writing text formulas, you can simply ask Copilot to extract the information you need.

For example:

• Extract the person's name
• Extract the job title
• Extract the email address

5. Summarize long notes

If a column contains meeting notes or customer feedback, Copilot can create a short summary for each row.

Example:

=COPILOT("Summarize each note in one sentence", A2:A15)

6. Generate keywords

If you're building a product catalog or website, Copilot can generate search keywords for each product description.

Example:

=COPILOT("Generate three search keywords for each product", A2:A20)

7. Build a schedule or plan

You can also use Copilot to generate structured content based on information already in your worksheet, while adding a second prompt to guide the result.

Option 1: Meal planner

Suppose you have a weekly meal plan, and cell C2 contains a dietary preference.

Meal	Suggestion
Breakfast
Lunch
Dinner
Snack

Cell C2:

Vegetarian

Formula:

=COPILOT(
"Suggest one meal for each row.",
B5:B8,
"Dietary preference:",
C2)

Here, the first prompt tells Copilot what to generate, while the second prompt adds extra context from another cell.

You can change Vegetarian in cell C2 to High Protein or Gluten Free, and the suggestions update.

Option 2: Employee training plan

Week	Training Topic
Week 1
Week 2
Week 3
Week 4

Cell C2:

New customer support representative

=COPILOT(
"Suggest one training topic for each week.",
B5:B8,
"Role:",
C2)

Change the role to Sales Manager or Data Analyst, and you get a completely different plan.

Option 3: Travel packing list

Category	Items
Clothes
Electronics
Toiletries
Documents

Cell C2:

3-day business trip

=COPILOT(
"Suggest what to pack for each category.",
B5:B8,
"Trip type:",
C2)

Things worth knowing

• It works best with text rather than calculations.
• It isn't designed for heavy math or very large datasets.
• You need a Microsoft 365 Copilot license that's tied to a work or school account.
• The results are AI-generated, so you should always review them.
• If you want to keep the current AI-generated results, copy them and use Paste Special → Values, since they may change the next time the workbook recalculates.

I'm still experimenting with it, but these are the first use cases that actually felt practical instead of just being AI demos.

Have you found any prompts that work especially well?

I recently got access to the new =COPILOT() worksheet function in Microsoft 365 and spent some time trying it on everyday spreadsheet tasks.

Here are a few examples that I thought were genuinely useful.

1. Generate sample data

You can ask Copilot to generate things like:

• Fictional project names
• Employee job titles
• Product descriptions
• Customer comments

Example:

=COPILOT("Generate 10 fictional project names")

Possible result:

Project Name
Project Aurora
Green Horizon
Northstar Initiative
BluePeak
Atlas Connect
NovaWorks
Summit Path
BrightBridge
Vertex One
Clearview Project

2. Categorize text automatically

Suppose column A contains support tickets.

Support ticket
Can't sign in to Microsoft 365
Outlook won't send emails
Printer is offline
Excel crashes when opening a large file
Forgot my Windows password
Outlook is running very slowly

In the next column, ask Copilot to categorize each ticket.

Example prompt:

=COPILOT("Assign a category to each support ticket", A2:A7)

Possible result:

Category
Account
Email
Hardware
Software
Account
Email

3. Estimate priority

If you have a long list of requests or issues, Copilot can suggest which ones should be reviewed first.

Example:

=COPILOT("Rate each request as High, Medium, or Low priority", A2:A20)

Obviously you'd still review the results, but it's a nice starting point.

4. Extract information from messy text

Suppose one cell contains:

John Smith
Senior Engineer
john@contoso.com

Instead of writing text formulas, you can simply ask Copilot to extract the information you need.

For example:

• Extract the person's name
• Extract the job title
• Extract the email address

5. Summarize long notes

If a column contains meeting notes or customer feedback, Copilot can create a short summary for each row.

Example:

=COPILOT("Summarize each note in one sentence", A2:A15)

6. Generate keywords

If you're building a product catalog or website, Copilot can generate search keywords for each product description.

Example:

=COPILOT("Generate three search keywords for each product", A2:A20)

7. Build a schedule or plan

You can also use Copilot to generate structured content based on information already in your worksheet, while adding a second prompt to guide the result.

Option 1: Meal planner

Suppose you have a weekly meal plan, and cell C2 contains a dietary preference.

Meal	Suggestion
Breakfast
Lunch
Dinner
Snack

Cell C2:

Vegetarian

Formula:

=COPILOT(
"Suggest one meal for each row.",
B5:B8,
"Dietary preference:",
C2)

Here, the first prompt tells Copilot what to generate, while the second prompt adds extra context from another cell.

You can change Vegetarian in cell C2 to High Protein or Gluten Free, and the suggestions update.

Option 2: Employee training plan

Week	Training Topic
Week 1
Week 2
Week 3
Week 4

Cell C2:

New customer support representative

=COPILOT(
"Suggest one training topic for each week.",
B5:B8,
"Role:",
C2)

Change the role to Sales Manager or Data Analyst, and you get a completely different plan.

Option 3: Travel packing list

Category	Items
Clothes
Electronics
Toiletries
Documents

Cell C2:

3-day business trip

=COPILOT(
"Suggest what to pack for each category.",
B5:B8,
"Trip type:",
C2)

Things worth knowing

• It works best with text rather than calculations.
• It isn't designed for heavy math or very large datasets.
• You need a Microsoft 365 Copilot license that's tied to a work or school account.
• The results are AI-generated, so you should always review them.
• If you want to keep the current AI-generated results, copy them and use Paste Special → Values, since they may change the next time the workbook recalculates.

I'm still experimenting with it, but these are the first use cases that actually felt practical instead of just being AI demos.

Have you found any prompts that work especially well?

If you've ever opened a workbook where every worksheet is protected, unprotecting them one by one gets old fast.

For example, you receive a monthly report with 30 protected worksheets. Every sheet uses the same password, but Excel still makes you unprotect them individually.

Here are a few ways to handle it.

Method 1: Unprotect worksheets one by one (built into Excel)

Excel lets you remove protection from a worksheet by going to:

Review → Unprotect Sheet

Enter the password, then repeat the process for every protected worksheet.

This works well if you only have one or two sheets, but it quickly becomes repetitive in larger workbooks.

Method 2: VBA

If all worksheets use the same password, VBA can unprotect them all automatically.

Sub UnprotectAllSheets()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="your_password"
    Next ws

End Sub

Replace "your_password" with the worksheet password, then run the macro.

This is a good option if you're comfortable using VBA or need to repeat the task regularly.

Method 3: Kutools for Excel

Kutools for Excel includes an Unprotect Worksheets tool for workbooks where multiple worksheets share the same password.

Go to:

Kutools Plus → Protect Worksheets → Unprotect Worksheets

Kutools automatically lists all protected worksheets in the workbook. Select the worksheets you want to unprotect, click OK, enter the password once, and Kutools removes the protection from all selected worksheets.

Unprotect Worksheets

📌 Notes

• All selected worksheets must use the same password.
• You can choose to unprotect all protected worksheets or only selected ones.
• Protected chart sheets are not included in the worksheet list.

Which method should you use?

• One or two worksheets → Use Excel's built-in Unprotect Sheet.
• Same task repeatedly → Use the VBA macro.
• Need a user-friendly solution with control over which sheets to unprotect → Use Kutools Unprotect Worksheets.

If you've found a clever workaround that doesn't require VBA, I'd love to hear it.

Data copied from websites, PDFs, databases, or other systems often contains something that looks harmless: extra spaces, a hidden line break, a non-breaking space, or a few unwanted symbols mixed into the data.......

Those small issues can cause lookups to fail, make duplicates harder to spot, or leave values that look identical but are not actually the same.

Excel has several functions for cleaning different types of unwanted characters, and the best one depends on what is causing the problem.

Method 1: TRIM — remove unnecessary spaces

TRIM removes leading and trailing regular spaces and reduces repeated spaces between words to a single space.

=TRIM(A2)

Example:

John Smith → John Smith

This is usually the first function to try when text looks uneven because of extra spaces.

📌 Note: TRIM handles regular spaces, but it may not remove non-breaking spaces copied from websites.

Method 2: CLEAN — remove hidden non-printing characters

CLEAN removes many non-printing control characters that may appear in imported or copied data.

=CLEAN(A2)

These characters may be invisible, but they can stop otherwise identical values from matching.

For a more thorough basic cleanup, combine CLEAN with TRIM:

=TRIM(CLEAN(A2))

This removes many hidden characters and then cleans up the remaining regular spaces.

Method 3: SUBSTITUTE — remove or replace known characters

Use SUBSTITUTE when you know exactly which character needs to be removed or replaced.

To remove hyphens:

=SUBSTITUTE(A2,"-","")

Example:

AB-105-26 → AB10526

A particularly useful application is removing non-breaking spaces. These often appear after copying text from a web page and may look identical to normal spaces.

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

This replaces each non-breaking space with a regular space, then removes unnecessary spacing.

You can also nest SUBSTITUTE when several known characters need to be removed:

=SUBSTITUTE(SUBSTITUTE(A2,"@",""),"#","")

Method 4: REGEXREPLACE — remove characters by pattern

In supported Microsoft 365 versions, REGEXREPLACE is useful when the unwanted characters follow a pattern.

For example, remove all digits:

=REGEXREPLACE(A2,"\d","")

Michael 0011 → Michael

Keep only digits:

=REGEXREPLACE(A2,"\D","")

Phone: 123-456-7890 → 1234567890

Keep only letters and numbers:

=REGEXREPLACE(A2,"[^A-Za-z0-9]","")

AB-105@New! → AB105New

Replace one or more regular whitespace characters with a single space:

=TRIM(REGEXREPLACE(A2,"\s+"," "))

This is useful for text containing repeated spaces, tabs, or line breaks. For non-breaking spaces, replace CHAR(160) first:

=TRIM(REGEXREPLACE(SUBSTITUTE(A2,CHAR(160)," "),"\s+"," "))

Method 5: TEXTJOIN and dynamic-array functions — remove duplicate characters

When you need to keep only the first occurrence of each character, use:

=TEXTJOIN("",TRUE,UNIQUE(MID(A2,SEQUENCE(LEN(A2)),1)))

Example:

AABBCC1055 → ABC105

This separates the value into individual characters, removes duplicates, and joins the remaining characters again.

📌 Note: It removes repeated characters throughout the cell, not only consecutive duplicates.

Quick formula guide

Assume the original text is in A2.

Cleaning task	Formula
Remove leading, trailing, and repeated regular spaces	=TRIM(A2)
Remove non-printing control characters	=CLEAN(A2)
Remove hidden characters and extra regular spaces	=TRIM(CLEAN(A2))
Remove non-breaking spaces copied from websites	=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Remove a known character, such as a hyphen	=SUBSTITUTE(A2,"-","")
Remove all digits	=REGEXREPLACE(A2,"\d","")
Remove all letters	=REGEXREPLACE(A2,"[A-Za-z]","")
Keep only digits	=REGEXREPLACE(A2,"\D","")
Keep only letters	=REGEXREPLACE(A2,"[^A-Za-z]","")
Keep only letters and numbers	=REGEXREPLACE(A2,"[^A-Za-z0-9]","")
Replace repeated whitespace with one space	=TRIM(REGEXREPLACE(A2,"\s+"," "))
Remove line breaks	=SUBSTITUTE(A2,CHAR(10),"")
Replace line breaks with spaces	=SUBSTITUTE(A2,CHAR(10)," ")
Remove tab characters	=SUBSTITUTE(A2,CHAR(9),"")
Remove duplicated characters	=TEXTJOIN("",TRUE,UNIQUE(MID(A2,SEQUENCE(LEN(A2)),1)))

Clean different character types from one Kutools dialog

The native formulas work well, but each cleaning problem requires a different function or formula. Kutools for Excel brings the main character-removal options together in one dialog.

Select the cells, then go to:

Kutools → Text → Remove Characters

From there, you can remove:

• Numeric characters
• Alphabetic characters
• Everything except numbers
• Everything except letters
• Non-printing characters
• Everything except letters and numbers
• Any custom characters you enter
• Duplicated characters

For example:

Michael 0011 → remove Numeric → Michael

America 34-12234@2212* → remove Non-numeric → 34122342212

AB-105@New! → remove Non-alphanumeric → AB105New

• You can preview the cleaned results before applying them.
• The Skip non-text cells option is useful when the selected range contains both text and numeric values. For example, select it together with Numeric to remove digits from cells containing text while leaving cells that contain only numbers unchanged.

Kutools is especially convenient when the cleanup rules vary from one dataset to another and you do not want to build and remember a different formula for each task.

Did I miss any useful text-cleaning functions or tricks? I'd love to hear what you use.

When related information is stored in two different tables, you can merge them using a column they have in common. For example, one table may contain Product, Price, and Stock, while another contains Product, Supplier, and Category. The products may appear in a different order, and some may exist in only one table. The goal is to match the products and bring the related information from the second table into the first.

Here are three practical ways to do it in Excel.

Method 1: Merge two tables with XLOOKUP

For a straightforward match, XLOOKUP is usually the easiest formula method.

Suppose the main table contains:

Product	Price	Stock
Apple	2.50	80
Orange	3.20	45
Peach	4.00	60
Lemon	2.80	30

And the second table contains:

Product	Supplier	Category
Peach	Supplier B	Fruit
Apple	Supplier A	Fruit
Lemon	Supplier C	Fruit
Pear	Supplier D	Fruit

To bring Supplier and Category into the main table, use:

=XLOOKUP(lookup_value,lookup_range,return_range,"")

• lookup_value = the value in the main table you want to match
• lookup_range = the matching column in the second table
• return_range = the column or columns you want to bring back

In this example, we will use:

=XLOOKUP(product_in_main_table,product_list_in_2nd_table,supplier_and_category_range,"")

If a product from the main table is not found in the second table, the final "" returns a blank.

📌 Note: XLOOKUP returns the first matching record it finds. It also does not automatically add products that exist only in the second table.

Method 2: Merge the tables with Power Query

Power Query is a better choice when you want to combine the tables more completely, especially when some records appear in only one table.

First, convert both ranges to Excel Tables with Ctrl + T.

Load each table into Power Query using:

Data → From Table/Range

Then:

1. Open the main table query.
2. Go to Home → Merge Queries.
3. Select the second table.
4. Click the Product column in both tables as the matching column.
5. Choose the join type you need.
6. Click OK.
7. Expand the merged column and select the fields you want to bring in.

For example, a Left Outer join keeps every product from the main table and adds matching Supplier and Category information.

If you want to keep products that appear in either table, use a Full Outer join instead.

After finishing, choose Home → Close & Load.

Power Query is especially useful when the source tables change regularly, because you can refresh the merge later instead of rebuilding it.

Method 3: Merge tables with Kutools for Excel

Kutools for Excel provides a Tables Merge feature for doing the same type of matching through an interface.

Go to Kutools Plus → Tables Merge

Select the first table as the main table, then select the second table as the lookup table.

Choose Product as the matching column.

Then select the columns you want to bring into the main table, such as Supplier and Category.

Kutools gives you more options for situations where the two tables do not contain exactly the same records. For example, you can:

• Add unmatched rows from the lookup table
• Handle duplicate matches
• Add new columns
• Update existing columns
• Highlight updated cells

So if Pear exists only in the second table, you can choose to add it to the main table instead of leaving it out.

Which method should you use?

Use XLOOKUP when you simply need to bring matching information into an existing table.

Use Power Query when the tables have missing or extra records, or when the merge needs to be refreshed regularly.

Use Kutools Tables Merge when you want an interface that can handle matching, missing rows, duplicates, added columns, and updates without building formulas.

When several values are stored in one Excel cell, you may need to separate them by a comma, space, line break, or another delimiter.

For example:

Apple,Orange,Banana,Grape

You may want each item in a separate column or a separate row. Here are three practical ways to do it.

Method 1: Text to Columns

For a quick one-time split into separate columns, Excel's built-in Text to Columns feature is one of the easiest options.

1. Select the cells you want to split.
2. Go to Data → Text to Columns.
3. Choose Delimited, then click Next.
4. Select the delimiter used in your data, such as Tab, Semicolon, Comma, or Space. For another delimiter, select Other and enter it.
5. Choose where you want the results to appear, then click Finish.

For example:

Apple,Orange,Banana,Grape

becomes:

Apple | Orange | Banana | Grape

💡 For cells containing line breaks: select Other, click its box, and press Ctrl + J to enter a line break as the delimiter.

📌 Limitation: Text to Columns can split data across columns, but it doesn't provide an option to split the results into rows.

Method 2: TEXTSPLIT formula

If you want a formula-based solution, TEXTSPLIT is much more flexible. It can split the same text into either columns or rows.

📌 Note: The TEXTSPLIT function is available in Microsoft 365 and Excel 2024 or later.

Suppose A2 contains:

Apple,Orange,Banana,Grape

Split by comma into columns

=TEXTSPLIT(A2,",")

Result:

Apple | Orange | Banana | Grape

Split by comma into rows

Use the comma as the row delimiter instead:

=TEXTSPLIT(A2,,",")

Result:

Apple
Orange
Banana
Grape

Split by a space

=TEXTSPLIT(A2," ")

Split by a line break

For line breaks, use CHAR(10):

=TEXTSPLIT(A2,CHAR(10))

To split the line-separated values into rows:

=TEXTSPLIT(A2,,CHAR(10))

Split by multiple delimiters

TEXTSPLIT can also recognize more than one delimiter. For example, if your data contains both commas and semicolons:

=TEXTSPLIT(A2,{",",";"})

Excel will split the text whenever it finds either delimiter.

Because TEXTSPLIT returns a dynamic array, the results spill automatically into the neighboring cells.

Method 3: Split cells with Kutools for Excel

If you frequently need to split data in different ways and prefer an interface instead of formulas, Kutools for Excel provides a Split Cells tool.

1. Select the cells you want to split.
2. Go to Kutools → Merge & Split → Split Cells.
3. Choose Split to Columns or Split to Rows.
4. Choose how you want to split the contents. You can use delimiters such as a space, comma, line break, semicolon, or specify another delimiter.
5. Click OK, then select where you want to place the results.

💡 Tip: Kutools supports more flexible splitting options. You can choose Other to enter your own separator, including one or multiple characters. You can also separate text and numbers automatically, or use Fixed Width to split the content

Split text and numbers separately

Kutools’ Text and Number option can separate the text and numeric characters in a cell into two parts.

In the Split Cells dialog, choose Text and number, then choose whether to split the results into columns or rows.

For example:

Product105 → Product | 105

A10B35 → AB | 1035

In the Split Cells dialog, choose Text and number, then select whether you want the results placed in columns or rows.

This can be especially useful when cleaning imported data where letters and numbers are stored together.

Which method should you use?

Text to Columns is great for a quick one-time split into columns.

TEXTSPLIT is the most flexible built-in formula option, especially when you need the results in rows or want them to update automatically when the original cell changes.

Kutools Split Cells provides an interface for splitting into either rows or columns using common or custom delimiters. It can also separate mixed text and numbers, or split content by a specified number of characters.

Sometimes a cell contains both text and numbers, and you only need the numeric part.

For example:

Order105A
INV-2026-001
Qty: 48 units

There are a few ways to handle this, depending on your Excel version and how consistent the text pattern is.

Method 1: REGEXEXTRACT — simplest for Microsoft 365

If you're using Microsoft 365, REGEXEXTRACT is probably the cleanest option for extracting numbers from mixed text. It works especially well when you only need the first continuous group of digits, rather than every number in the cell.

To extract the first continuous group of digits:

=REGEXEXTRACT(A2,"\d+")

Example:

Order105A → 105

INV-2026-001 → 2026

Qty: 48 units → 48

Method 2: Extract every digit with TEXTJOIN + MID + SEQUENCE

If you want to remove all letters and symbols and combine every digit into one result, use:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Example:

Order105A → 105

INV-2026-001 → 2026001

Qty: 48 units → 48

Here's the basic idea:

• SEQUENCE creates the character positions.
• MID pulls each character individually.
• Multiplying by 1 keeps numeric characters and causes an error for letters.
• IFERROR removes those nonnumeric characters.
• TEXTJOIN joins the remaining digits together.

This is more complicated than REGEXEXTRACT, but it gives a different result when numbers appear in several places.

Method 3: Use LEFT, MID, or RIGHT when the number is always in the same position

If your data follows a predictable structure, you may not need a complicated formula at all.

For example:

INV-2026

If the four-digit number is always at the end:

=RIGHT(A2,4)

Result:

2026

Or if you know exactly where the number starts and how long it is:

=MID(A2,start_position,number_of_characters)

This is usually the easiest approach when the format of every cell is consistent.

Method 4: Use Power Query to extract numbers from mixed text

Power Query is another good option, especially when you already use it for cleaning imported data or want a repeatable process that can be refreshed later.

Steps

1. Select your data and press Ctrl + T to turn it into an Excel Table.
2. Go to Data → From Table/Range to open the data in Power Query Editor.
3. Go to Add Column → Custom Column.
4. Enter a name for the new column, such as Extracted Number.
5. In the Custom column formula box, enter the formula that matches the type of number you want to extract.
6. Click OK.
7. When finished, go to Home → Close & Load to return the results to Excel.

Here are some useful M formulas. Replace [Column1] with your actual column name.

What you want to extract	M formula
All digits from the cell	Text.Select([Column1], {"0".."9"})
Digits plus decimal point and minus sign	Text.Select([Column1], {"0".."9",".","-"})
Number before specific text, such as " units"	Text.Select(Text.BeforeDelimiter([Column1], " units"), {"0".."9"})
Number after known text, such as "Qty: "	Text.Select(Text.AfterDelimiter([Column1], "Qty: "), {"0".."9"})
Content between two delimiters, such as [105]	Text.BetweenDelimiters([Column1], "[", "]")
Convert the extracted digits to an actual number	Number.FromText(Text.Select([Column1], {"0".."9"}))

For example:

Order105A → 105
INV-2026-001 → 2026001
Qty: 48 units → 48

💡 Things to keep in mind:

• Text.Select keeps every allowed character it finds. So allowing "-" and "." works well for something like Balance: -120.50, but may not be appropriate for IDs such as INV-2026-001.
• Text.Select returns text, even when the result looks numeric. If you need the result for calculations, wrap it with Number.FromText, such as: Number.FromText(Text.Select([Column1], {"0".."9"}))

Method 5: Kutools for Excel — Extract Text

For mixed data where the pattern varies from row to row, Kutools for Excel provides an Extract Text tool that can pull different parts of a cell, including numbers only.

Select your cells, then go Kutools → Text → Extract Text.

From there, you can extract:

• The first N characters
• The last N characters
• Characters between specified positions
• Text before specific text
• Text after specific text
• Numbers only
• Text based on custom rules using wildcards

For this task, choose Extract the number.

💡 You can also select Insert as a formula if you want the extracted result to remain linked to the original data.

This is handy when you have a large range of mixed text and don't want to build different formulas for different patterns.

Which method makes sense?

Use REGEXEXTRACT when you're on Microsoft 365 and need a particular numeric pattern.

Use TEXTJOIN + MID + SEQUENCE when you want to collect every digit from the cell.

Use LEFT, MID, or RIGHT when the number always appears in a predictable position.

Use Kutools Extract Text when the data varies and you'd rather handle it through a dialog instead of building formulas.

Sometimes a cell contains both text and numbers, and you only need the numeric part.

For example:

Order105A
INV-2026-001
Qty: 48 units

There are a few ways to handle this, depending on your Excel version and how consistent the text pattern is.

Method 1: REGEXEXTRACT — simplest for Microsoft 365

If you're using Microsoft 365, REGEXEXTRACT is probably the cleanest option for extracting numbers from mixed text. It works especially well when you only need the first continuous group of digits, rather than every number in the cell.

To extract the first continuous group of digits:

=REGEXEXTRACT(A2,"\d+")

Example:

Order105A → 105

INV-2026-001 → 2026

Qty: 48 units → 48

Method 2: Extract every digit with TEXTJOIN + MID + SEQUENCE

If you want to remove all letters and symbols and combine every digit into one result, use:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))

Example:

Order105A → 105

INV-2026-001 → 2026001

Qty: 48 units → 48

Here's the basic idea:

• SEQUENCE creates the character positions.
• MID pulls each character individually.
• Multiplying by 1 keeps numeric characters and causes an error for letters.
• IFERROR removes those nonnumeric characters.
• TEXTJOIN joins the remaining digits together.

This is more complicated than REGEXEXTRACT, but it gives a different result when numbers appear in several places.

Method 3: Use LEFT, MID, or RIGHT when the number is always in the same position

If your data follows a predictable structure, you may not need a complicated formula at all.

For example:

INV-2026

If the four-digit number is always at the end:

=RIGHT(A2,4)

Result:

2026

Or if you know exactly where the number starts and how long it is:

=MID(A2,start_position,number_of_characters)

This is usually the easiest approach when the format of every cell is consistent.

Method 4: Kutools for Excel — Extract Text

For mixed data where the pattern varies from row to row, Kutools for Excel provides an Extract Text tool that can pull different parts of a cell, including numbers only.

Select your cells, then go Kutools → Text → Extract Text.

From there, you can extract:

• The first N characters
• The last N characters
• Characters between specified positions
• Text before specific text
• Text after specific text
• Numbers only
• Text based on custom rules using wildcards

For this task, choose Extract the number.

💡 You can also select Insert as a formula if you want the extracted result to remain linked to the original data.

This is handy when you have a large range of mixed text and don't want to build different formulas for different patterns.

Which method makes sense?

Use REGEXEXTRACT when you're on Microsoft 365 and need a particular numeric pattern.

Use TEXTJOIN + MID + SEQUENCE when you want to collect every digit from the cell.

Use LEFT, MID, or RIGHT when the number always appears in a predictable position.

Use Kutools Extract Text when the data varies and you'd rather handle it through a dialog instead of building formulas.

A lot of everyday accounting work in Excel comes down to the same tasks: matching IDs, totaling transactions by criteria, flagging exceptions, rounding amounts, extracting invoice numbers, and tracking due dates.

Here are 7 formulas and function combinations that are particularly useful for those jobs.

1. XLOOKUP — match IDs and return related information

XLOOKUP is useful when you have an ID in one table and need to pull the matching information from another. Think vendor IDs, account codes, invoice numbers, or customer IDs.

Syntax:

=XLOOKUP(lookup_value,lookup_range,return_range,"Not found")

For example, you could look up a VendorID from an invoice and return the corresponding vendor name.

💡 The last argument also lets you decide what Excel should show when there is no match. Because XLOOKUP handles this itself, you don't need to wrap it in IFERROR just to deal with missing IDs.

2. SUMIFS — total transactions that meet several conditions

SUMIFS comes in handy when a simple SUM isn't enough.

Syntax:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

For example, to total expenses by category, department, and date range, the structure could look like this:

=SUMIFS(amount_range,category_range,category,department_range,department,date_range,">="&start_date,date_range,"<"&end_date)

💡 This is useful for questions like: How much did the Sales department spend on Travel during this period?

3. IF + AND/OR — flag transactions that need attention

IF becomes much more useful when you combine it with AND and OR.

Syntax:

=IF(OR(condition1,AND(condition2,condition3)),"Review","OK")

An accounting rule might look something like:

=IF(OR(amount>=high_amount,AND(days_overdue>limit,paid_status<>"Yes")),"Review","OK")

So an invoice could be flagged for review if the amount is unusually high or if it's overdue and still unpaid.

This is useful for exception reports where you don't want to manually inspect every row.

4. ROUND — keep amounts consistent

Sometimes a value displayed as 12.35 actually contains additional decimal places underneath. That can cause unexpected differences in calculations or comparisons.

Syntax:

=ROUND(number,num_digits)

For two decimal places:

=ROUND(amount,2)

It's a simple one, but very useful when working with calculated amounts, allocations, taxes, exchange rates, and other values where decimal precision matters.

5. MID + FIND — pull invoice numbers out of longer text

Imported bank or transaction descriptions often contain useful IDs mixed in with other text.

If the invoice number always follows the same pattern, MID and FIND can extract it.

Syntax:

=MID(text,FIND(start_text,text),number_of_characters)

For example, in cell A2:

Payment received | INV-2026-001 | Ref: 7781

If the invoice number always starts with INV- and is 12 characters long, the formula structure would be:

=MID(A2,FIND("INV-",A2),12)

Result:

INV-2026-001

This works particularly well when the position of the invoice number changes but its prefix and length stay consistent.

6. TODAY + EOMONTH — track due dates and month-end

These two date functions are useful for reports that need to update automatically as time passes.

To calculate the number of days until an invoice is due:

=due_date-TODAY()

To calculate the number of days until the last day of this month:

=EOMONTH(TODAY(),0)-TODAY()

💡 A positive result means there are days remaining. A negative result means the invoice is already overdue.

7. IFERROR — clean up formula errors when you actually need it

IFERROR is useful when a formula can produce an error and you'd rather display something meaningful.

Syntax:

=IFERROR(formula,value_if_error)

For example:

=IFERROR(calculation,"Check data")

These aren't accounting-specific functions, of course, but they fit surprisingly well into everyday accounting workflows—from invoice matching and reconciliations to expense summaries, exception checks, aging reports, and month-end work.

Converting a PDF to Word is useful when you need to edit the text, reuse content, or make changes without retyping everything.

Here are two ways to do it.

Method 1: Open the PDF directly in Microsoft Word

This is the easiest built-in method for a normal text-based PDF.

1. Open Microsoft Word.
2. Go to File → Open → Browse.
3. Select the PDF file.
4. Word will let you know that the PDF will be converted into an editable Word document.
5. Click OK.
6. Save the converted file as a .docx document.

This works well for PDFs that mainly contain text and simple tables.

💡 One thing to keep in mind: complex layouts, graphics, columns, or heavily formatted tables may not look exactly the same after conversion.

Method 2: Use Kutools for Word

Kutools for Word provides a direct PDF-to-Word conversion tool and can also convert multiple PDF files at once.

Go to:

Kutools → PDF and Word

Then choose:

• Convert Single PDF File to Word — for one PDF
• Convert Multiple PDF Files to Word — for batch conversion

Select the PDF file or files you want to convert, then follow the prompts to complete the conversion.

💡 The batch option is especially useful when you have several PDFs and do not want to open and convert them one by one.

Quick comparison

Open directly in Word
Best for quickly converting one standard PDF with no extra tools.

Kutools for Word
Best when you want a more direct conversion workflow or need to convert multiple PDF files in one go.

Here are some Excel shortcuts I use all the time for common data cleanup and processing tasks. They’re especially handy for cutting down on repetitive clicking when:

• Filtering data
• Searching and replacing
• Removing duplicates
• Splitting text
• Selecting visible cells only
• Sorting data quickly

A few of them, especially Ctrl + E and Alt + ;, can save a surprising amount of time.

Shortcut	What it does
Ctrl + E	Auto-fill data based on patterns
Ctrl + H	Replace values or text quickly
Ctrl + F	Search for specific content
Ctrl + Shift + L	Enable or disable filters
Alt + ↓	Open the selected column filter
Alt + A + S + A	Sort selected data ascending
Alt + A + S + D	Sort selected data descending
Alt + A + M	Delete duplicate entries
Alt + A + E	Separate text into multiple columns
Alt + ;	Select visible cells only
Ctrl + -	Remove selected rows or cells
Alt + A + C	Clear filters from the worksheet
Ctrl + G	Jump to a specific cell or range

Normally, when you copy a formula to another location, Excel adjusts its relative references.

For example, copying:

=A2+B2

one row down changes it to:

=A3+B3

That is useful most of the time, but sometimes you need an exact copy with every reference left unchanged. The method depends on whether you are copying a whole range of formulas or just one formula.

Copy a range of formulas without changing references

Method 1: Temporarily convert the formulas to text

This built-in workaround is useful when you need to copy several formulas at once.

1. Select the formula range.
2. Press Ctrl + H.
3. Replace = with a unique temporary string, such as #=.
4. Copy and paste the range to the new location.
5. Replace #= with = in both the original and copied ranges.

Removing the leading equal sign temporarily makes Excel treat the formulas as text. As a result, their references do not change when the cells are copied.

💡 Choose a temporary string that does not already appear in the selected cells, and make sure the replacement is limited to the correct ranges.

Method 2: Use Kutools Exact Copy

Kutools for Excel provides an Exact Copy tool that copies a complete formula range while keeping all relative, absolute, and mixed references unchanged.

1. Select the formulas you want to copy.
2. Go to Kutools → Exact Copy.
3. Confirm the selected source range.
4. Keep Copy formatting checked if you also want to preserve the formatting.
5. Click OK.
6. Select or enter the first cell of the destination range.
7. Click OK again.

The selected range is copied to the new location with the formulas exactly as written. This is especially useful for large formula blocks or when you also need to preserve the original formatting.

Copy one formula without changing references

For a single formula, you do not need to convert a whole range to text.

1. Select the formula cell.
2. Click in the formula bar or press F2.
3. Select the full formula text and press Ctrl + C.
4. Select the destination cell.
5. Paste the formula and press Enter.

Because you are copying the formula text rather than the cell, Excel keeps the original references.

💡 You can also use absolute references when the referenced cells should always stay fixed:

=$A$2+$B$2

Press F4 while editing a reference to switch between relative, absolute, and mixed references.

Quick comparison

For a range of formulas: use Find and Replace, or Kutools Exact Copy for a quicker direct method.

For one formula: copy the formula text from the formula bar.

For references that should always stay fixed: convert them to absolute references before copying.