## Extracting Text From Variable Length Strings

After I wrote about how to extract characters from the left, right or middle of a text string in Excel, I received a few inquiries about extracting text from strings which don’t seem to have a fixed size, and vary in length.

For example, if the data is something like this

HQ-1022-PORT
LONDON-4053-LANDED
HOUSTON-2488-WEST
SINGAPORE-3133-LEEDON

You want to separate out the first word (HQ, LONDON, HOUSTON, SINGAPORE etc.)

Can’t Use LEFT Function
You can’t use a LEFT function, because a left function needs to know the number of characters you want to extract. And this itself is variable… There are 2 characters in HQ, 6 in London, 7 in Houston etc.

One of the ways I have discovered is to find the location of the first Hyphen (-), and extract all characters from the left of it.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Finding the location of the First Hyphen
We can use the FIND function to find the location of the first hyphen.

If A2 contains HQ-1022-PORT, we can use the formula as:

=FIND(“-“,A2)

The answer would be 3. This means that the hyphen is the third character in the string. This is perfect. Now we know that we need n-1, that means 3-1, which is 2. We need 2 characters from the Left of this number.

So we can write another formula as

=LEFT(A2,  FIND(“-“,A2)-1 )

The resulting answer would be HQ. Copy the formula down to other cells, and you should be able to extract HOUSTON, SINGAPORE etc. without much hassle, and without having to write another, different formula.

One size fits all formula to extract a variable length string from a piece of text. And pretty easily too!

Do you have any other method? How would you solve this challenge?

Cheers,
Vinai Prakash

P.S. – I am teaching a 2 day course on Data Interpretation & Analysis in Singapore on Feb 9-10, 2015, and another on March 9-10, 2015. If you would like to learn techniques to analyze data and create management reports, you can attend this program.

## Quick Tip: Re-Sorting a List or Table in Excel

Of course you know how to sort data in Excel! Simply click on the Sort button, or click on the mini A-Z or Z-A chicklet buttons, and voila! the list is sorted. And if you want more control, you can try Custom Sorting, and Advanced Sorting with this Method.

The problem is that a sorted list often gets out of sync, if you keep adding or editing data in the table. And if you use a Custom Sorting sequence, you can’t simply click on the single column sorting chicklets any longer  🙁

So you have to go to Custom Sort, and click it again to get the list sorted one more time.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

A Quick Way to Sort Your Custom Sorted Table in Excel
Fortunately, there is a quicker and shorter way. All you need to do is to click on the Reapply button, which shows up once you click on the Sort menu on the Home Tab.

There’s a shortcut – Ctrl + Alt + L

This makes the current list or table sorted again. Even if you had multiple columns in the custom sorted list.

What I have done is to put this Reapply button on my Quick Access Toolbar in Excel. This makes it much snappier to apply it anytime, anywhere in Excel.

Hope you like this quick tip. It has saved me quite a few clicks, and a lot of time searching for something in a not so sorted table…

Cheers,
Vinai Prakash

PS: Check out how to sort in a unique manner – not ascending , not descending. Sorting it Your Way!

Also, let me know if there is anything in Excel that bothers you…. anything where you could get some help. Simply post it into a comment or email it to me, and I’ll see what I can do to help you out 🙂 – Vinai

## Multiple Ways to Count in Excel

Counting the number of cells containing values, counting number of cells meeting a certain criteria, counting names, counting cells in a range…. you name it, there is a need to count in Excel. And there are multiple ways to count in Excel. Some techniques are more helpful than others, and some can provide unique insights which others can’t produce.

Let’s look at the different ways to count in Excel, and their relative benefits.

1. Count the number of cells with values: The simplest way is to use the inbuilt function Count. Select the cell range, and out comes the cou nt of cells in the range.

Count only works with counting of Numeric information. If you try to count names, you will get a ZERO.

To count alphanumeric values – values with names, categories, text, serial numbers etc., then you must use the variation of Count – called COUNTA. This function is to count Alphanumeric data.

2. Using a Criteria to Count specific Cells: There are several ways to count the cells that meet a certain criteria. The first method treats the data as a database, and uses the Database Functions within Excel.

=DCOUNT(Database, Field, Criteria)

Here A4:C12 is the entire data, B4 is the cell column that we want to count, and the criteria is Age should be greater than 30 years (B1:B2) cells.

The answer for this count is 5. See if you got this correct.

The database functions have been existing in Excel since 1995. They treated Excel data as a database like Oracle, Dbase etc. These database functions DSUM, DCOUNT, DAVERAGE etc. are quite useful, and are available for backward compatibility, although the same work can be accomplished by other functions equally well too.

3. Using COUNTIF Function to count cell conditionally. This method evaluates the cells against the condition, and if it matches, it is counted.

=COUNTIF(Cells, criteria)

Here, the cells that contain the data is provided first, and the second argument is the criteria. The headings are not really needed. Each cell in the first range is checked against the criteria, and if the criteria matches, the record is counted, otherwise it is ignored.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

And if you have multiple criteria to check for, you can use the COUNTIFS which can take multiple criteria arguments.

The formula in the picture captures the count of cells that are having age of >30years, and Salary less than \$3000. Answer is 3.

So as you can see, there are multiple functions, and ways to count cells in Excel. It depends on whether you want to count numbers or textual data. It also depends if you have conditions or not.

Use these functions to count in multiple ways… Happy counting!

Cheers
Vinai Prakash

## Top 10 Time Saving Tips in Using Microsoft Excel

If you are a data warrior, chances are that you use Excel extensively in your day to day work.

And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.

1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.

2. Clean up Your Data  – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually,  or you can request Excel to remove the duplicate rows completely, automatically.

3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.

=MOD(ROW(),2)=0

This function would tell us if the row is an even row or an odd row, and then we could colour it.

But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.

And then you can also use the formula above, and use conditional formatting.

4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.

5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively.  Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.

If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.

6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.

7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!

8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.

So instead of saying =G3+C3-F3, you can now calculate Salary as

=Basic_Pay + Allowance – Tax

This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.

9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:

• Control N: New workbook
• Control `: View All Formulas
• Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
• Alt + =: Auto sum formula is inserted
• Ctrl + Shift + Enter: Enter an Array Formula
• F9: Calculates all the worksheets in all open workbooks

10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.

Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.

If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.

Cheers,
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma

### Are you facing any problem in using Excel? Any Question?

P.S.  : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.

And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂

## Creating a New Calculation in an Excel Pivot Table

Ever since I published the article on how to display both values and percentages in a pivot table & How to Get Running Totals in Pivot Tables, I am getting more and more emails asking about the other features and tricks within Pivot Tables in Microsoft Excel.

One such useful feature is the ability to add a new calculated field, which is not even present in the base data set, but can be derived from the existing fields.

We can call such a field as Adding a New Calculated Field in the Pivot Table. Once added, it is available to you in a pivot table as a regular field, and can be summed, counted, averaged, etc. with ease.

To Add a Calculated Field, go to the Pivot Table Options Menu, and Find the “Fields, Items & Sets” drop down menu in the ribbon.

Click and select the Calculated Field from the drop down menu.

Type a field name in the Name: field.

We will key in the formula in the next blank cell. Do note that Excel already provides you with a Zero there. You need to remove the Zero first. And then begin to type your formula.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

All the existing Fields are listed in the Field List below the formula.

Let’s say we have to calculate Bonus, which is 10% of the Sales Commission.

So the field Name we would give would be called Bonus.

The Formula would be =Sales Comm * 0.10

Click OK to close this popup menu.

You will now notice that a new field called Bonus is active in the Field List.

Double Click or Drag it to the Values Section of the Pivot Table Layout.

Voila! You have now created an new calculation called Bonus, based on existing values within the Pivot table, and it is now available for analysis in the Pivot Table.

You can use this simple technique to create as many pivot table fields, and enhance your analysis.

If you would like more such tips, you may subscribe to the ExcelChamp Excel Tips Newsletter or Excel Tips RSS Feed.

Cheers,
Vinai Prakash

### Are you facing any problem in using Excel? Any Question?

PS: If you liked the tip, please do Like us, Stumble Upon on the Left Socials Bar, or Digg it!

## How to Replace Blanks With Any Value or Zeros in Excel

This is a common problem. You import some text file, and half of it seems blank. The problem is that if it is showing blanks, you can’t sum the values.

To replace each blank with a zero is time consuming, boring and stupid. We want a fast method that works in just a few clicks, and saves us loads of time for our families 🙂

There are many ways to achieve this task of replacing Blank with Zeroes, or Replacing Blanks with Any Other Values in Excel.

Method 1: Use the GoTo Method to Find & Replace

Click on Home > Find & Select > GoTo Special. This will open up a new popup window.

Choose Blanks Radio Button, and Click OK.

All the Blank Cells will get highlighted. Do not worry. Simply type a Zero or any other value that you want to put in the blank cells.

Do this in the currently active cell. Press

together.

Voila! The keyed in Values is showing in all the Blank Cells now.

Method 2: Use an IF condition

For example, if you want to selectively key in 0 for blanks, key in this formula below.=IF(A2=””,0,A2)

This formula checks if the cell A2 is blank or not. IF it is blank, it will replace it with a 0, or else it will not change it.

Once done, you can drag the formula to the selected cells. Works beautifully!

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Method 3: Variation of the IF Method, using the ISBLANK function

Now we have an in-built function that can check if the cell ISBLANK or not.

=IF(ISBLANK(A2),0,A2)

Same Magic!

If you have any other favorite method, do share it with us below in the comments!

Questions About Any Feature in Excel?

And Subscribe to our Weekly Excel Tips Newsletter, so we can send you more Short cuts & some Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe!

Cheers,
Vinai Prakash, PMP, ITIL, GAP, Six Sigma
Founder of ExcelChamp.Net

Vinai conducts 1-2 day seminars and Training Sessions on Microsoft Excel all over the world, helping companies maximize their productivity in using Microsoft Excel. If you would like to find out more, simply contact us.

## Dynamic Chart Titles in Excel

A chart title in Excel is  usually hard coded.

This means that when underlying data changes, the heading does not change… it remains static… unless someone notices it, and modifies it manually.

Thus, headings like “Sales for September 2014”, “Revenue for Q1, 2014” etc. are not wise, and may even reflect the wrong picture… specially if the data is updated, but the heading is not.

Most people do not realize that it is possible to customize the heading, and it could be a formula, or based on any cell in the worksheet.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

How To Customize A Chart Heading?

Simple. First use formulas to build a dynamic heading in a particular cell in Excel. It could be like:

=”Sales for ” & A1

Second, Click on the Heading in the chart. Once the heading is highlighted, move to the formula bar. Either key in this formula in the formula bar, or refer to the cell which contains this formula.

Now the chart will show a Dynamic Chart Heading… that changes, based on the data selected.

Hope this helps! Let me know if you have any other challenges in Excel.

Cheers,Vinai Prakash