Jan
16
How to number columns in Excel and convert column letter to number
The tutorial talks about how to return a column number in Excel using formulas and how to number columns automatically. Continue reading
"Excel" category, page 4
Jan
12
ISNA function in Excel with formula examples
When Excel cannot find what it is asked for, a #N/A! error appears in a cell. To intercept and handle such errors, you can use the ISNA function. Continue reading
Jan
6
How to change Excel CSV delimiter to comma or semicolon
Imagine this: you want to export your Excel data to another application. Whatever CSV option you use, the result is a semicolon-separated file instead of comma-separated you really wanted. The setting is default, and you have no idea how to change it. Continue reading
Dec
22
How to highlight active row and column in Excel
When viewing a large worksheet for a long time, you may eventually lose track of where your cursor is and which data you are looking at. To know exactly where you are at any moment, get Excel to automatically highlight the active row and column for you! Continue reading
Dec
15
VLOOKUP to compare two columns in Excel for common values and missing data
When you have data in two different lists, you may often need to compare them to see what information is missing in one of the lists or what data is present in both. Comparison can be done in many different ways, which method to use depends on exactly what you want from it. Continue reading
Dec
9
How to use and store custom functions in Excel
Today we’ll continue exploring custom Excel functions. Now that you know how to create UDFs, let’s dig a bit deeper and learn how to use and store user-defined functions in Excel. Continue reading
Dec
8
RegEx in Excel: using regular expressions in formulas
At first sight, Excel has everything you could ever need for text string manipulations. Hmm… what about regular expressions? Oops, there are no built-in Regex functions in Excel. But no one says we cannot create our own ones :) Continue reading
Dec
2
Debug user defined functions
As you already know how to create UDFs (and, I hope, you’ve also tried applying them in your Excel), let’s dig a bit deeper and see what can be done in case your user defined function is not working. Continue reading
Dec
1
How to remove whitespace and empty lines in Excel with Regex
Whichever input data you are using, you'll hardly encounter a dataset without spaces. In most cases, whitespace is good. In some situations, however, it may become evil - extra spaces can mess up your formulas and make your worksheets almost unmanageable. Continue reading
Nov
25
Custom UDF function vs VBA macros: advantages and drawbacks
This article will help you understand the differences between UDF and Macro. Also, custom functions have many advantages, but there are also many limitations when using them. Continue reading
Nov
24
How to extract substrings in Excel using regular expressions (Regex)
Microsoft Excel provides a number of functions to extract text from cells. Those functions can cope with most of string extraction challenges in your worksheets. Most, but not all. When the Text functions stumble, regular expressions come to rescue. Continue reading
Nov
11
Why is Excel User Defined Function not working?
This article shows how to add a help text to UDF and explains why these functions are not recalculated. You will see the differences between volatile and non-volatile custom functions. Continue reading
Nov
10
Converting CSV to Excel: problems and solutions
The name CSV (comma separated values) implies the use of comma to separate data fields. But that is in theory. In practice, many so-called CSV files separate data using other characters such as semicolon or tabs. The lack of standard spawns various problems with CSV to Excel conversions. Continue reading
Nov
4
How to create custom user defined functions in Excel
This article will introduce you to user defined functions in Excel. You’ll see different variants of user defined functions and learn how to create and apply them. Continue reading
Nov
3
How to merge multiple CSV files into one Excel workbook
If you often export files in the CSV format from different applications, you may end up having a bunch of individual files relating to the same subject matter. The question is - is there a simple way to import them all into a single Excel workbook? Continue reading
Oct
27
Excel Regex to remove certain characters or text from strings
Have you ever thought how powerful Excel would be if someone could enrich its toolbox with regular expressions? We have not only thought but worked on it :) And now, you can add this wonderful RegEx function to your own workbooks and wipe out substrings matching a pattern in no time! Continue reading
Oct
20
Excel Regex to find and replace strings using regular expressions
Microsoft Excel provides a number of options to replace one piece of text with another. Why would one want to complicate things with regexes? Because Excel's standard features can only process an exact string that you specify. Continue reading
Oct
13
Excel Data Validation using regular expressions
When it comes to restricting user input in Excel worksheets, Data Validation is indispensable. It can do anything you can possibly imagine. What if I want to allow entering only valid email addresses or strings that match a specific pattern? Alas, that's not possible. Regex you say? Hmm… that might work! Continue reading
Oct
6
Regex Match in Excel: regular expression matching with examples
When you need to find a certain value in a range of cells, you use the Excel MATCH function. When looking for a specific string in a cell, the FIND and SEARCH functions come in handy. And how do you know if a cell contains information that matches a given pattern? Obviously, by using regular expressions. Continue reading
Sep
29
CONCAT function in Excel to join text strings
Traditionally, string concatenation in Excel is done with the help of the CONCATENATE function or the ampersand symbol. In modern Excel, these old-school methods give way to CONCAT - a newer and improved function to combine text from multiple cells into one cell. Continue reading
Sep
22
How to add text or character to every cell in Excel
When working with text data in Excel, you may sometimes need to add the same text to existing cells to make things clearer. For example, you might want to put some prefix at the beginning of each cell, insert a special symbol at the end, or place certain text before a formula. Continue reading
Sep
15
Convert Excel table to range and turn data range into table
Table is one of the most useful things in Excel that makes managing, calculating and updating data a lot easier. Under certain circumstances, however, a table format may not suite your needs. This short tutorial will teach you how to turn a table into a normal range without losing your data. Continue reading
Sep
8
How to remove characters/text from string in Excel
In this tutorial, we will look at the most common cases of removing characters in Excel. Want to delete specific text from multiple cells? Or maybe strip the first or last character in a string? Or perhaps remove only a specific occurrence of a given character? Whatever your task is, you will find more than one solution for it! Continue reading
Aug
18
How to remove special (unwanted) characters from string in Excel
When importing data to Excel from somewhere else, a whole lot of special characters may appear in your worksheets. Even more frustrating is that some characters are invisible, which produces extra white space before, after or inside text strings. Continue reading
Aug
11
How to delete text before or after a certain character in Excel
In the recent couple of articles, we've looked at different ways to remove characters from strings in Excel. Today, we'll investigate one more use case - how to delete everything before or after a specific character. Continue reading
Aug
4
How to remove first (left) or last (right) characters in Excel
When working with unstructured text data, you often need to parse it to retrieve relevant information. This article will teach you a few simple ways to remove any number of characters from the left or right side of a text string. Continue reading
Jul
20
How to remove text and leave numbers in Excel or vice versa
Imagine this: you receive raw data for analysis and find out that numbers are mixed with text in one column. In most cases, it will certainly be more convenient to have them in separate columns for closer examination. Continue reading
Jul
14
How to remove table formatting in Excel
By default, Excel tables are equipped with a number of great features including predefined table styles. In some situations, however, you may want to remove formatting keeping all other functionality of a table. This tutorial will teach you how to quickly do that. Continue reading
Jul
7
How to highlight duplicate text strings or words in Excel cell
Excel Conditional Formatting makes it possible to highlight duplicates in every possible way you can think of. But, as usual, there is a "but" - conditional formatting rules work on a cell level while you may want to highlight duplicate text rather than entire cells. This can only be done with macros. Continue reading
Jun
30
How to find and replace multiple values at once in Excel (bulk replace)
How do people usually search in Excel? Mostly, by using the Find & Replace tool, which works just fine for single values. But what if you have tens or even hundreds of items to replace? Luckily, there are a few more effective methods to perform mass replace in Excel, and we are going to look at each of them in detail. Continue reading