Change & replace tools

The macros in this group are responsible for changes you want to make to specified entities — characters, words, cells, or ranges. Before using them, it’s recommended to first run macros from the Find or Extract groups, so you know exactly which elements should be modified and which shouldn’t. For example, if all the text in your cells is lowercase and you want to find place names and change their case to proper case, it’s better to detect those place names first.

Menus inside this group:

Characters

The character change menu includes several submenus:

• Advanced macros for changing letter case in Excel. This includes changing case for place names (cities), first letters of sentences, first letters of each word, and automatic detection of abbreviations;
• Macros for converting diacritic chars to Latin;

Words

This menu contains macros that allow you to:

• Add a character before each word — or after them;
• Capitalize each word, place names, or your own list of words;

Cells

Changing cells — or rather, their contents — with !SEMTools includes:

• Add a character to each cell — at the beginning, end, or both sides. Or any sequence of characters;
• Fill empty cells in a range with values from above or below;
• Swap words in a cell;
• Sort words inside a cell in alphabetical order (normalization);
• Translate cell text into another language.

Numbers

Often you need to quickly modify numeric ranges in cells. Since numbers have their own specifics, I’ve put them in a separate menu. With it, you can perform arithmetic operations:

• Add a number to an entire column or row;
• Subtract a number from a column or row;
• Multiply a column or row by a number;
• Similarly, divide a column or row by a number;
• Raise numbers to a power.

Other mathematical actions:

• Round a column of numbers;
• Calculate a share of the total sum;
• Calculate cumulative share;
• Count repetitions;
• Count the occurrence number of an element in a range.

In addition to mathematical operations, you may encounter problems when converting text to numbers and vice versa. That’s why this menu also includes procedures to:

• Convert numbers stored as text into real numbers;
• Spell out numbers;
• Spell out amounts with cents in different formats.

Ranges

Entire cell ranges can also be transformed:

• Sort the contents of a range with regular sorting or by endings;
• Convert tables into summaries in one click — sum up data or merge row data (concatenate).

Replace

This menu allows you to bulk replace values in a selected range:

• Words and word lists;
• Any patterns by substring match;
• By regular expression.

This post is also available in KZ and RU.