I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.
Tag Archives: text
Converting Text Numbers and Reversing Signs in Excel
Paste Special to the rescue
Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special.
Converting a Tricky Date Format
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
Converting a number to a text number in Excel
An easy formula
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
The Handy Functions in Excel – LEFT and RIGHT
Simple Defaults
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?
Using a Number as Text in Excel Made Easy
When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task.
Generating Random Text Entries in Excel
Create random first and last name combinations
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
Excel Flowchart Technique
Excel has great charts to help you visual your numbers, but it can also allow you to use flowcharts to help visualise numbers in a different way and help explain relationships between numbers and how they are formulated.
Cell Line Breaks Via a Formula
When creating a cell that joins text together you may want to include a line break between parts of the text. A function and a format combined can provide that.
Two Excel Functions to Format Numbers as Text
Using numbers in automated text sentences can be frustrating. Typically you don’t want to display decimals, but you do want to use the comma format.
Extracting the Column Letter in Excel
There can be times when identifying the current or a specific column letter reference is useful.
Insert a Note in an Excel Formula
If you want to place a note inside an Excel formula you can use an old function that is, in most cases, redundant. The N function was used in early spreadsheets, but is hardly ever used in modern formula.
Counting Characters in Excel
Another useful SUMPRODUCT technique
If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?
Quick as a Flash with Excel 2013
A new feature that's not perfect yet
Excel’s brand new Flash Fill feature can be a time saver, but it’s not fool proof. Flash Fill is a new feature in Excel 2013.
How to get to first place in Excel
Or second and third
If you need to convert a number to its ordinal so that 1 = 1st and 4 = 4th then there is no built function to do it, but you can write a formula to do it.
Excel’s shorthand shortcut
Save typing
Excel has a feature that can save you a lot of typing, it’s called AutoCorrect. Yes it’s the same system that fixes your spelling mistakes. Well did you know it has two handy features?
Creating Dynamic Text Box entries in Excel
Flexible text
Text boxes are flexible because you can place them anywhere on your spreadsheet.
You can link a text box to the contents of a cell.
Joining Text in Excel the quick and easy way
BAN CONCATENATE!
The CONCATENATE function is redundant. You don’t to need to use it. There is a much quicker + easier way to join text together.