## יום חמישי, 28 ביולי 2022

### How to split a multi-line cell into separate cells

How to split a multi-line cell into separate cells?

There are at least two known solutions to this problem: splitting multiple lines within a cell to separate cells, each containing one line of the original, multiple-line cell.

1.    Text-to-Columns
This method uses the delimiter CTRL+J.
Typing these two keys together, creates the linefeed (hidden) character.

2.    Power Query

In Power Query, you choose:
Split Column->By Delimiter->split using special characters->

Insert special character->linefeed

But what I want to show you today is a method I've invented, which is (I believe) both the fastest and the simplest.
The solution can "materialize" thanks to the new TEXTSPLIT function in Excel 365 (I'm an "Office Insider" user but I believe that this function is already available to all 365 users, and if not, it will soon be).

So, without further ado let's dive into my solution.

1.    Our  multi-line cell looks like this (Cell A1):

PIC 1: Cell A1 is multi-line and we want to split each line into a separate cell

2.    We define the delimiter. In cell I1 we type: =CHAR(10)

CHAR(10) is the ASCII linefeed character.

PIC 2: defining the delimiter

3.    In cell B4, we type the following formula:

=TEXTSPLIT(A1, I1)

And the contents of cell A1 (5 lines) are split into 5 cells: B4-F4.

PIC 3: splitting horizontally

And of course, we can split vertically as well:

PIC 4: splitting vertically

That's all, folks.

Hope you liked it.

## יום רביעי, 27 ביולי 2022

### A simple trick that will save you a lot of time

A simple trick that will save you a lot of time

Suppose you have a digit in each of the cells: A1, B1, C1

And you want to combine the digits into a number:

You could do it thus:

Or thus:

But a far better way would be:

Why?
The latter solution is dynamic. If you change the range of cells to be combined, you don't touch the formula. You only modify the Parameter.

## יום שני, 25 ביולי 2022

### Five Excel Table Shortcuts worth Knowing

Five Excel Table Shortcuts worth Knowing

1.    CTRL+T (or: CTRL+L a lesser-known shortcut):
Creates a table

2.    CTRL+SHIFT+8 (CTRL+*):
Selects the entire table,
including the header row and the total row (if present)

3.    CTRL+SHIFT+T (a toggle shortcut):

4.    CTRL+A:
Selects the entire table, without the total row (if present)

5.    CTRL+SHIFT+drag mouse:
Duplicates the entire table

### Excel - 4 methods to copy a cell N times (according to parameter)

Excel - 4 methods to copy a cell N times (according to parameter)

In this post I'm going to show you 4 different methods to copy a cell's value

And duplicate it N times (N is a parameter)

Method 1

Method 2

Method 3

Method 4

## יום שבת, 23 ביולי 2022

### Excel - Find the most recent visit date of your patients

Excel - Find the most recent visit date of your patients

A friend of mine, who is a doctor, came to me seeking help with a problem in Excel.
He said: "I have this list of patients and their visit dates, all mixed up in an unsorted list.

I want to know, for each patient, which is his/her most recent visit date.
And I'd prefer the simplest method in Excel since I don't want to use complicated formulae.

So here is the data:

Pic No.1: Original List

And here a simple solution using a Pivot Table:

Pic No.2: Pivot Table Solution

## יום חמישי, 7 ביולי 2022

### Excel – Intersection: Part II

Excel – Intersection: Part II

In the previous installment I introduced the INTERSECTION feature in Excel. We saw some neat, simple solutions to analysing data gathered from 4 US states over a period of 12 months.

In this installment, I'm going to show the combination of the Intersection operation with other Excel functions.

Again, these examples will demonstrate the ease of use and the advantage over more "traditional" methods.

Example 1: Second smallest number (State and value)

Pic 1: second smallest number of cases in August

Example 2: Intersection & OFFSET

Pic 2: Intersection & OFFSET (NY without the first 2 months)

Example 3: Intersection & FILTER

Pic 3: Intersection & FILTER (CA, only cases > 5000)

Example 4: Intersection & SORT

Pic 4: Intersection & SORT (Asc. order, TX) with matching months

Example 5: 5 last months of FL

Pic 5: Last 5 months in FL

## יום שבת, 2 ביולי 2022

### Excel - What is Intersection? Why is it useful? - Part I

Excel – Intersection: What is it? Why is it useful?

The following dataset displays the number of juvenile delinquency cases opened in four US states during the year 2020. (not really, the data are fabricated 😊 )

Pic 1: the dataset

Now, suppose you want to analyse the dataset. For example:
How many cases were opened in Californian in May?

This can easily be solved in various methods,
for example, with the following formula:

Or with this one:

However, there's a far better, much simpler method in Excel for cross tab data.

Unfortunately, it is lesser known. It is called Intersection.
The intersection Operation is ideal when dealing with cross tab data: a column data intersecting a row data.

The intersection operation does not refer to cells or ranges, but to names.

So, how do we use this feature?

First of all, we need to define the names (taken from cells B1:E1, the column   headers and from cells A2:A13, the row headers)

So, we Create names from selection as explained in the picture:

Pic 4: Create Names from Selection

If we take a close look at the definitions created in the Name Manager, we can clearly see that each State has 12 values (the values of the 12 months) and that each month has four values (one for every state).

Pic 5: Name Manager's definitions

And now let's turn to some cool applications of Intersection.

Example No.1:
Remember the question we asked ourselves at the beginning?
How many cases were opened in Californian in May?

So, instead of a lengthy formula, we can have the solution in a very short formula:
=CA May

Pic 6: How many cases were opened in California in May

The formula consists of two elements: the column (CA) and the row (May), separated by the intersection operator " ".

So simple, so easy, so obvious….

Example No.2:

How many cases were opened in Texas alone during the entire year?

Pic 7: How many cases were opened in Texas during the entire year?

Example No.3:

How many cases were opened in Texas in the first 4 months?

Pic 8: How many cases were opened in Texas during Jan.-Apr.?

Example No.4:

How many cases were opened in Texas and California in the first 4 months?

Pic 9: No. of cases opened in Texas & California in Jan-Apr

Example No.5:

How many cases were opened in January (excluding Texas)?

Pic 10: How many cases were opened in January (excluding Texas)

Example No.6:

Total cases in Texas, California and Florida (altogether)

Pic 11: Total cases in Texas, California and Florida (altogether)

## יום שישי, 1 ביולי 2022

### How to remove empty rows in a range (without VBA, without PQ, without formulae, even without the mouse)

Excel – How to remove empty rows in a range

The following technique explains, step by step, how to get rid of empty rows within a range.

Please note that this technique does not use either VBA, PQ or formulae.

It doesn't even use the mouse 😊