Tags: SUBSTITUTE

Removing dependent validation list from cell for one case

{4 Comments}

In cell B6, a user can select one of the following entries from a validation drop down list - Reason1, Reason2 and Reason3.  If the user selects either Reason1 or Reason2, a dependent validation drop down lists should appear in cell C6.  However, if the user select Reason3 in cell B6, the dependent validation list from cell C6 should vanish thereby allowing the user to enter any value in cell C6.

You may refer to my solution in the this workbook.

Extract number from an alphanumeric string

{18 Comments}

Given an alphanumeric string, one may want to perform the following

Extract phone number from the string

Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself.  These numbers may be mobile numbers and/or mobile numbers.  Furthermore, PIN codes may also be mentioned in the address string.

One may want to extract only the phone numbers to another column.

You may refer to the my solution in this workbook.

Extract one specific 20 digit number from the string

Assume cell descriptions which contains two 20 digit numbers occurring anywhere in the string.  Once may want to extract only that 20 digit number which has the word New before it.

You may refer to my solution in this workbook.

Remove special characters and numbers from an alphanumeric string

{4 Comments}

Hi,

Assume the following alphanumeric strings in range A3:A6

A-2222 Mohan 12-4 #$% Singh$%&***
**** Sanchit #$% Pal$%&***
$^%$^$ Sudhir #$% Si$$$ngh$%&***
#@234 Krishan #$% Oman$%&***

In range B3:B6, one may want to extract only the text values i.e. drop special characters and numbers from the values in range A3:A6.  In range B3:B6, the result should be:

A Mohan Singh
Sanchit Pal
Sudhir Singh
Krishan Oman

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.

You may refer to my solution in this workbook.

Display auto filter criteria in a cell

{2 Comments}

Assume data in range D6:E11 - months in D6:D11 and numbers in E6:E11.  Headings are in D5:E5.  On filtering multiple values in column D, one may want to view the filtered criteria in cell (separate criteria by comma for multiple selections in auto filter drop down).

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.  This add in will enable us to use the following two functions

1. UNIQUEVALUES(); and
2. MCONCAT()

You may refer to my solution in this workbook.

Determine stock transfer from multiple locations

{0 Comments}

The objective is to determine the quantity of stock (of a particular type) to be transferred from "Stock surplus" locations to "Stock deficient" ones.  If all the requirements cannot be met from one location, tap other locations.  The final output should show the location from where stock is being transferred.  Furthermore the order of determining "Stock transfer location" is descending order of stock available in "Stock surplus" locations.  Stock available in "Stock surplus" locations should be determined after setting off quantity already transferred to other locations.

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.

You may refer to my solution in this workbook.