top of page

Range Macros

One Utility, +30 Handy Options

Revolutionize your Excel workflow with Range Macros Utility.

No more building formulas or performing time-consuming multi-step operations. With Range Macros, simply select the action you need to make and let the tool do the work for you.

Accelerate your Excel workflow with Range Macros, featuring over 30 useful utilities for working with text, numbers, dates, data, charts, and more. With Range Macros, you can streamline your work, saving time and producing cleaner, error-free results.

Range Macros Utilities

Text Macros

1. Text Macros Utilities

Clean your non-numeric data with ease by selecting a range in Excel. The Range Macros tools allows you to quickly remove unwanted characters, leading or trailing spaces, and other non-numeric elements from your data.
 

Clean
Text

Trim text in selected cells without using formulas. Removes leading spaces, trailing spaces, extra spaces and non-printing characters. No inputs are required to run this macro.

Change
Case

Change the case of text in selected cells without using formulas: UPPER CASE, lower case, Proper Case, Sentence case. Use the dialog box to pick your choice.

Remove a Character
from Selection

Remove any character or string from selected text. Including special characters like "*". Use the dialog box to type the substring to remove.

Remove Accents

Remove accents and diacritical marks from selected text. Accented characters are replaced by its Unicode version (e.g. áéíóú is changed to aeiou). No inputs are required to run this macro. 

Extract Text from Cells

Extract a text string from left, right or middle of Cell. Use the form to specify the position and number of characters to extract. You can extract text using several rules at once.

Add Text to Cell Values:

Add text to selection. Use the form to specify the text to add and its start position. You can add text using several rules at once.

Text Macros Functions

STRIPDIACRITICS FUNCTION:

  • A new function that removes accents and diacritical marks from text, returning an unaccented version of the character." Parameters can be typed using Excel’s “Formula Builder”

PLEASE NOTE:

  • This Macro will not retain individual character formatting in selected cells. Formatting applied to the entire cell is preserved.

  • This Macro will not work on a protected sheet.

HOW TO USE

  • A cell range must be selected before running this utility.

  • Text Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range.

  • Text Macros will only work in cell ranges. Blank cells and cells with formulas are ignored.

  • You can select large ranges, including entire rows or columns.

  • You can re-do this Macro with shortcut CTRL-OPT-Y.

Number Macros

2. Number Macros Utilities

Simplifies and enhances your data cleaning process. Select a range of numerical data and apply formatting functions to make it consistent and easier to analyze.

Convert Text Numbers
to Numbers

Convert numbers stored as text into numbers. No inputs are required to run this macro.

Convert Numbers
to Text: 

Store numbers as text numbers by adding a quote (') at the beginning. No inputs are required to run this macro.

Convert 0.000,00
to 0,000.00

Change decimal sign from "," to "." List separators are changed accordingly. No inputs are required to run this macro.

Convert 0,000.00
to 0.000,00

Change decimal sign from "." to "," List separators are changed accordingly. No inputs are required to run this macro.

Fix Negative
Signs 

Fix most common problems with imported negative numbers. You will be presented with 4 options:

  • Fix Trailing Negative Signs: "1-" is converted to -1

  • Change Sign: Number multiplied by -1

  • Make All Numbers Positive: abs(number)

  • Make All Numbers Negative: abs(number) * -1

Convert Date
(dd-mm mm-dd) 

Extract a text string from left, right or middle of Cell. Use the form to specify the position and number of characters to extract. You can extract text using several rules at once.

Remove Time from
Date and Time

Returns date from a date and time value: trunc(date). No inputs are required to run this macro.

Remove Date from
Date and Time

Fix most common problems with imported negative numbers. You will be presented with 4 options:

  • Fix Trailing Negative Signs: "1-" is converted to -1

  • Change Sign: Number multiplied by -1

  • Make All Numbers Positive: abs(number)

  • Make All Numbers Negative: abs(number) * -1

Number Functions

CHECK WRITER Functions Parameters:

Parameters can be typed using Excel’s “Formula Builder”:

  • SPELLNUMBER: Spell number in English.

  • SPELLNUMBER_ESP: Spell number in English.

  • SPELLNUMBER_FRA: Spell number in English.

  • SPELLNUMBER_POR: Spell number in English.

SPELLNUMBER Functions Parameters

  • Number: Number to spell.

  • Coin: Currency Name (optional). Defaults: [EN} = Dollar, [ESP] = Peso, [POR] = Real, [FRA] = Euro.

  • Coin_plural: Currency Name in Plural (optional). If left empty, will add plural assuming it’s a regular noun.

  • Spell_cents: Optional. Cents are spelled only if True. Default = True.

  • CoinCent: Name for currency hundredths (optional). Default: [EN] = Cent; [ESP] = Centavo; [POR] = Centavo; [FRA] = Cent.

  • CoinCent_Plural = Name for currency hundredths in Plural (optional). If left empty, will add plural assuming it's a regular noun.

  • Prefix: Optional. Type any text to go at the beginning. Default = Empty.

  • Suffix: Optional. Type any text to go at the end.
    Default = Empty."

  • Text case. Optional. 1= UPPERCASE; 2 = lowercase; 3 = Proper Case; 4 = Sentence case. Default = Proper Case."

ONLY ON MAC:

  • You can also spell numbers on any language installed on your machine with the Spell Number utilities.

HOW TO USE:

  • A cell range must be selected before running this utility.

  • Number Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range.

  • Number Macros will only work in cell ranges. Blank cells and cells with formulas are ignored.

  • You can select large ranges, including entire rows or columns.

  • You can re-do this Macro with shortcut CTRL-OPT-Y.

PLEASE NOTE:

  • This Macro will not retain individual character formatting in selected cells. Formatting applied to the entire cell is preserved.

  • This Macro will not work on a protected sheet.

Date Picker

3. Date Picker Macros

Convenient calendar that adds a new input method for dates in Spreadsheets.

Date
Picker

Convenient graphical calendar that adds a new input method for dates in Spreadsheets.

Text Date
Picker

A text only variation of our Date Picker, designed for fast input.

How to use Date Picker

  • A cell range must be selected before running this utility.

  • Date Picker is available on main ribbon menu under the “Insert Tab” or as a contextual menu when right-clicking on a range. For added convenience, a button is also provided on the “Insert Tab” from the top menu.

  • You can re-do this action with the shortcut CTRL-OPT-Y.

Please Note:

  • This Macro will not work on a locked cell inside a protected sheet.

  • Date Picker’s behavior is different on Windows machines.

Formula Macros

4. Formula Macros Utilities

Edit your formulas and names fast.

Paste - Exact
Formulas

Paste formulas without changing cell references. Original formulas come from the selected range. Use the dialog box to provide the first cell of the destination range. Multiple selections are not allowed.

Change Formula References Absolute/Relative

Changes formula reference

to any choice of:

• Relative row/ Absolute column: = $A1

•  Absolute row/ Relative column: = A$1

•  Absolute all: = $A$1

•  Relative all: = A1

Convert All Formulas
into Values

Use Shift + Control + V to instantly convert a range into values.

List All Names
(Name Manager)

List all named ranges and table names in Name Manager.

Use the resultant form to easily:

  • Delete invisible names: Delete all hidden names in the currently open Excel file.

  • Apply names on every worksheet: Select a name and apply it everywhere.

  • Delete names and fix dependent formulas: Select a name and delete it. Formulas in dependent cells will be fixed.

  • Edit any name: Select a name and do a quick edit.

Apply Name of Selected
Range in Workbook

Select a name and apply it everywhere.

All cell references will be replaced by the named range on every sheet.

 

This utility can also be run from the

Name Manager.

 

Delete Name of Selected
Range and Fix Formulas

Select a name and delete it. Formulas in dependent cells will be fixed. This utility can also be run from the Name Manager.

 

Count/ Highlight All Cells

with Error in Sheet

 

This utility will count error cells and

highlight them.​

 

How to use Formula Macros

  • A cell range must be selected before running this utility.

  • Formula Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range. For added convenience, a button is also provided on the “Formulas Tab”.

  • A Name Manager is also provided for easy access to our formula macros. 

  • Formula Macros will only work in cell ranges. Blank cells and cells with constants are ignored.

  • You can select large ranges, including entire rows or columns.

  • You can re-do this Macro with the shortcut CTRL-OPT-Y.

Please Note:

  • This Macro will not work on a protected sheet.

  • Names starting with _xl (used for Excel formula compatibility) are excluded from listings.

Range Macros

5. Range Macros Utilities

Clean, format, and compare ranges.

Clean Ranges utilities

Combine Cell
Contents Up

Combine Cell Contents using a delimiter. Text is joined in the uppermost cell.

You can select a range with more than one column at once and the utility will repeat itself on each.

Only single selection areas are allowed. Use the dialog box to pick a delimiter. Your choice of delimiter will be remembered until Excel is closed

Combine Cell
Contents Left: 

Combine Cell Contents using a delimiter. Text is joined in the leftmost cell. You can select a range with more than one row at once and the utility will repeat itself on each. Only single selection areas are allowed. Use the dialog box to pick a delimiter. Your choice of delimiter will be remembered until Excel is closed.

Replace Blank Cells
with Zeros: 

Fill with zeros all empty cells in a range.

Delete Blank Rows / Columns
from Selection

Delete the entire row/column within the selection if the ENTIRE row contains no data. Multiple selections are not allowed.

Format Range tools

Unmerge and Center
Across Selection

Convert merged cells in selection to "Center Across Selection". Only previously merged cells are centered.

Highlight Alternate
Rows in Range

Format simple ranges as if they were Excel Tables. A dialog will be presented to pick highlight colors.

Multiple selections are not allowed.

Change Border Color/Thickness
in Range

Change Border Colors without affecting thickness/styles. Only existing borders are modified.

How to use Range Macros

  • A cell range must be selected before running this utility.

  • Range Macros are available via the 101 Macros Menu on the “Home Tab” or as a contextual menu when right-clicking on a range.

  • Range Macros will only work in cell ranges. Other objects are ignored.

  • You can select large ranges, including entire rows or columns.

  • You can re-do this Macro with shortcut CTRL-OPT-Y.

PLEASE NOTE:

  • This Macro will not work on a protected sheet.

NO UNDO IS AVAILABLE

  • If Autosave is turned off, a message to save the file will appear before the utility is run.

  • The message will not show again during the session if “Do not save" is selected.

bottom of page