Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.

Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.



SUBSTITUTE is a great function/Formula in MS Excel that you may not have aware of, but now that you are about to learn, you will probably find everyday uses for it in Data Analysis.

In Microsoft Excel, you use the SUBSTITUTE function/Formula when you wish to replace specific text in a text string.

There are three pieces of information that are required for the SUBSTITUTE function/Formula in which a fourth is optional.

SUBSTITUTE SYNTAX:-

SUBSTITUTE(text,old_text, new_text, instance_num)
 

Text is the text string or cell containing text for which you want to substitute characters.
 

Old_text is the text you want to replace.
 

New_text is the text you want to replace it with.
 

Instance_num [Optional] specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Examples of SUBSTITUTE Function:-

Example 1:
Replace the 2nd occurrence of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/",2)
Result:      ABC-123/456-789

Example 2:
In "ABC-123-456-789" replace all occurrences of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/")
Result:      ABC/123/456/789

Because the instance number (i.e. 4th piece of information) is omitted from the function/Formula, Excel replaces every occurrence.

Example 3:
Something that you may need to do is replace text with nothing. In that case, use two double quotes (e.g. "") in the 'with this text' section. I frequently need to do this when I import account numbers, that include dashes, from my accounting system but need to use them without the dashes in Excel.

Formula:   =SUBSTITUTE("T-01-02-740-65","-","")
Result:      T010274065

You could also accomplish this with Find & Replace but you would need to do this every time the data is updated. Also, there will be times when you don't want to affect the original cells.

Substitute Function Excel

Example 4:
As with most other things in Excel, you can use cell references to supply information to SUBSTITUTE function/Formula.

In the previous example if your account numbers are in column B starting at row 10, you can enter =SUBSTITUTE(B10,"-","") in cell C10 and copy the formula down the column.

This is just a small taste of how the SUBSTITUTE function/Formula can be used for everyday applications.

Like most of these tips, the more you practice using this, the more ways you will think of to use it.

Read More

How to Freeze Panes for Wide Spreadsheets in MS Excel?

An Alternative trick To Freeze Panes For Wide Spreadsheets.

When using wide spreadsheets, you will often need to work with data on the far right of the sheet but also keep visible the related data on the left. You may already know that a good solution for this is Freeze Panes. With Freeze Panes, you select the column to the right of the one you want to freeze and, from the View tab, click Freeze Panes, Freeze Panes. Now you can scroll to the right while the frozen column remains visible on the left.

Freeze Panes is a good solution but is not without its drawbacks.

We often find that when we are moving around on the right side of my worksheet using the arrow keys, we sometimes scroll too far to the left and our data on the right scrolls off the screen. Then we need to scroll to the right again. This can get aggravating when it happens over and over.

Freeze Panes

One simple solution / way is to temporarily hide the columns that we are not using.

Another solution that works great for worksheets where we regularly need to hide (and unhide) specific columns is to use Grouping. Grouping is normally used for hiding the details of subtotaled data but, in some cases, it can work great for quickly hiding and unhiding columns.

This would also work for hiding and unhiding specific rows but in most cases.

How to hide specific column(s) using Grouping in MS Excel.

**Note that if you want to hide multiple columns, the columns for each grouping must be contiguous...

1) Select the (contiguous) columns (e.g. Columns D:Q) you want to apply Grouping to;

2) From the Data tab click Group. An outline bar and [-] symbol appears above the columns. When you click the [-] symbol, the columns will be hidden and the symbol changes to a [+];

3) You can select other (contiguous) columns to apply grouping to.

Once you have finished grouping your columns, you will have a simple and quick way to quickly hide and unhide these columns whenever you need to.

To permanently remove the Grouping (and outline symbols) from a column or group of columns, select the columns and, from the Data tab, click Ungroup.

Read More

How to Change Default Open/Save To Location of Excel with Example.

Change Excel's default Save to/Open Location.

If you want to put a specific location to Open or Save your Excel spreadsheet/File, you can do it which is very simple. There is a way to change the default location you get when you open or save workbooks in Excel. In the corporate world / Professional field or work, almost every people from us save our workbooks to a network drive rather then the My Documents folder on our PC's hard drive. So, if anyone would like to save his / her excel files on a network drive or somewhere other than the default My Documents folder, here is a solution in less than a minute.

When a person start Excel and open a file, by default, Excel first looks in the your My Documents folder. If he / she store most of your files in a different folder, they have to browse to it each time you open a file. When he / she save a new file or an existing file, Excel again defaults to your My Documents folder.

Now-a-days, so many of us are working on corporate networks that, more often than not, we open our files and save our files to a network folder. If you would like to change the folder location that Excel defaults to, there is a simple solution which very quick also.

1) In Excel 2010, from the File tab click Options. In Excel 2007, click the Office Button and then click Excel Options;

2) In the Excel Options dialog, click Save (on the left side) and look for the 'Default file location' field in the 'Save workbooks' section;

3) Type or paste the path of the folder you want to use as the default folder in the 'Default File Location' field;

4) Click OK.

Change Default Location

How to copy and paste a folder path in MS Windows?

If path of the new folder is very long, there's no need to type it all and take a chance on
misspelling it. Here's a trick you can use instead:

1) Open Windows Explorer, browse to and select the folder you want to use for your default Open/Save location;

2) Highlight the path in the Address Bar and press CTRL+C to copy it. (Note that Windows 7 doesn't show the path until you click in the blank area on the right end of the Address Bar. In Windows XP, if the Address Bar isn't displayed, from the View menu, select Toolbars, then Address Bar);

3) Switch back to Excel, click in the 'Default File Location' field and press CTRL+V to paste it.

Read More

How to Fix Wrongly Formatted Dates Quickly in MS Excel Spreadsheets with example.

Fix / Correct Wrong formatted Dates very quick in MS Excel. How to use Text to Column in Excel with Example.


Sometimes when we ever copy or import data into Excel from another source and we find that the date formats are all wrong. Take this example, your computer may recognize dates in mm/dd/yy format and you have just copied data from another source where the date format was dd/mm/yy.

So when you download, copy or insert data from another date format and open it in Excel, Excel doesn't recognize the dates. Here is an easy trick that allows you to quickly convert wrongly-formatted dates into a format you can work with.

In the given picture You'll notice that Excel recognizes some of the dates but for others it doesn't. Notice that the dates that Excel has recognized are right-aligned in the cells and those it didn't recognize, and assumed was text, are left-aligned. If you look closely you'll see that even the cells Excel did recognize as dates are sometimes wrong - the months and days are switched.

Date Format

It can be quite a task to fix all of these dates. There can be various techniques to solve this problem, but except this technique all of them are very time consuming. This is a simple little trick using Excel's 'Text to columns' option.

If you find yourself with a column of wrongly formatted dates Here's the method you can use...

1) Highlight the cells (only one column wide) containing the dates. You can select the entire column if you like;

2) On the Data tab click Text to Columns in the Data Tools group;

3) In the 'Convert Text to Columns' dialog, select the 'Fixed width' option and click the Next> button;

4) If there are any column break lines (vertical lines with arrows) through the data area, double-click each of them to remove them and then click the Next> button;

5) In the 'Column data format' section, you will see a dropdown next to the Date option, select the date format of the imported data (not the format you want to convert it to). So if your computer uses a month/day/year format (e.g. USA) and the dates you need to fix are displayed as day/month/year (e.g. UK/Canada), you will need to select the DMY format and Excel will convert them to the default date format of your computer. If your computer uses the dd/mm/yyyy format and you import US dates, you will have to choose MDY. Other formats such as YMD are also available if you import data that matches that format (e.g.20120531);

6) Click the Finish button.

Just like magic, Excel reads all of the imported MDY dates and converts them to DMY format (or DMY to MDY format). All of those dates have been fixed in just a few seconds.

Now, this one is the cool and fast way to do such a little but typical thing.

Read More

Restore R1C1 Style Cell Referencing To Pivot Tables in Excel.

Reset R1C1 Style Cell Referencing To Pivot Tables in MS Excel Spreadsheet With Example.
 

When you refer to a cell in a Pivot Table in Microsoft Excel, you will see a GETPIVOTDATA formula instead of a normal R1C1 style cell reference by default (e.g. =D5) as shown in below image.

GetPivotData

You can easily turn off this GetPivotData feature, If you want to use normal cell referencing.
 

WARNING! A major disadvantage of using R1C1 cell referencing is R1C1-style references does not adjust if the Pivot Table layout changes.

How to enable RICI Referencing in MS Excel Pivto Table? or How to disable / turn off the GetPivotData Function in Excel?


To enable R1C1 referencing follow the below steps...

1) Click any cell in the PivotTable and from PivotTable Tools click the Options tab;

2) In the PivotTable group, click the small arrow on the right side of the Options command. You will see a checkmark next to the 'Generate GetPivotData' option;

3) Click the 'Generate GetPivotData' to turn the option off. Note that this is a global setting that will remain off for all PivotTables until you turn it on again.

GetPivotData option On / Off

Now you can reference data in PivotTables using normal R1C1-style referencing with the Generate GetPivotData option turned off.

How to turn the option GetPivotData back on in MS Excel Pivot Table?

R1C1 Option disable

To turn the option back on, click the 'Generate GetPivotData' option again.

Remember :- when you switch bewteen GETPIVOTDATA and R1C1, previously created references to the Pivot Table do not change.

Read More

Add a Descriptive Text to Number Formats in Excel with Example.

How to use Custom Number Formats / Combine Descriptive Text to Number Formats in MS Excel with Example / Add Text to Number Formats through Custom Format.



If you want to Combine or include descriptive Text to your Number Formats in Microsoft Excel Spreadsheet you can also use custom number formatting.

How to apply custom format to the numbers / Numeric Values or How to add a descriptive Text to Number Formats in Microsoft Excel Spreadsheet with Example?

 
To add descriptive text in your number format, write / type the text you want to display in double quotes within the number format code.

So instead of using a formula that I used in my previous post which is given below....

="Report due " & TEXT(A4,"dddd mmmm d, yyyy at h:mm am/pm")

 

 ...you can get the same result by applying this custom number format to the cell(s) containing the date/time or a number.

"Report due " dddd mmmm d, yyyy "at" h:mm am/pm

Custom Number Format

In the above example you will find that the additional text 'Report due' and 'at' are typed in double quotes within the number formatting code.

Note:- It is necessary that you type text within double quotes but you don't have to use double quotes around text that doesn't contain any of the characters that Microsoft Excel uses in its number format codes,  like 'm,d,y,h,m,s,e,@'.

To avoid any confusion or errors, it's better to ALWAYS use double quotes around descriptive text in number formatting.

One of the biggest advantages adding text to number formats is that the value in the cell(s) is not affected. You can still sort, filter, and reference the cells in your formulas.

Custom Number Format

Read More

Concatenate Text And Numbers From Different Cells in MS Excel 2007 or 2010 with Example

Concatenate Text And Numbers From Different Cells / Combine Text And Numbers From Different Cells in MS Excel 2007 and 2010 With example.

There are so many situations when need to combine values from different cells into one / single cell. For Example when you have a table of names where the first names are in one column (B) and the last names are in another column (A). You can CONCATENATE (or combine) the contents of these two separate columns into a string of text in another column (C) using a formula such as =B1 & " " & A1. Note the blank space added betyouen the first and last name. You could also use CONCATENATE function =CONCATENATE(B1," ",A1) and get the same result in Microsoft Excel. But the ampersand (&) method is much simpler.

Concatenate Text

Now, suppose you want to combine / Join (Concatenate) some text with a numeric value from another cell, for example, "Payment is due 21-Sep-2012". Lets suppose M5 contains the payment date, you can create a formula using ="Payment is due " & M5. You will notice, however, that the payment date displays as 41173. The reason is that the formula uses the underlying value (41173) from the referenced cell rather than the formatted value you see in cell M5 (21-Sep-2012).

Concatenate Text with Numbers

Normal numeric cell formatting does not work with text strings. To format the number in your text string, you need to include the TEXT function in your formula. The TEXT function allows you to define the formatting of a number in a text string. In this example, your final formula would be ="Payment is due " & TEXT(M5,"dd-mmm-yyyy").


Understanding this opens up a whole new range of possibilities. Below are some other examples of combining text and numbers in Excel. There are many, many more.

Some of the other ways you can use this in your spreadsheets?

Concatenate Text with Number

Read More