Tags: RIGHT

Remove abbreviations appearing before a name

{2 Comments}

Assume there is a list of names appearing in range A2:A9.  Before these names, there are abbreviations such as Dr., Mr. Mrs. etc.  To worsen the situation, there may/may not be a space after the abbreviation.  A sample dataset may look like this:

Mr.Ramakrishna
Mr Ramakrishna
Mr. Ramakrishna
MrRamakrishna
PhD.Saurav
Dr (Mrs.) Indu Sharma
Dr. (Mr) Rakesh Singh
Mr. Mrinal Joshi

The task is to extract only the names in another column.

You may refer to my solution in this workbook.

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

You may watch a video of my work at this link

Worksheet formulas in a newly copied worksheet should point to the previous worksheet

{13 Comments}

Assume a worksheet with formulas referring to the previous worksheet.  When this sheet is copied by right clicking the sheet and selecting Move or Copy > Copy, formulas in this newly created sheet, should change to refer to the previous sheet.

In other words, if sheet2 has formulas referring to sheet1, then when sheet3 is created (by Move or Copy > Copy of sheet2), the formulas in sheet3 should refer to sheet2.

You may refer to my solution and Instructions in this workbook.

Extract text from a custom formatted cell

{23 Comments}

In range G5:G44, some numbers are entered.  These numbers are custom formatted with General" Dr" for debit entries and General" Cr" for credit entries.

One may want to extract the Dr and Cr in range H5:H44.

The following process will get the desired result:

1. Press Ctrl+F3 > New and in the Name box, type cell_format
2. In the Refers to box, enter this formula and click on Close

=GET.CELL(53,data!$G5)

3. In cell H5, enter this formula and copy down till cell H44

=RIGHT(cell_format,2)

Dr and Cr should now get appear in range H5:H44.