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 […]

Read More →

Extract number from an alphanumeric string

{ 72 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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

Extract a report showing parameters beyond tolerance limits

{ 0 Comments }

Depending upon tolerance limit specified for every technical parameter, extract a report which lists down all those day wise parameters which breach tolerance limits.  The question and solution have been elaborately explained in the workbook.

Read More →

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 […]

Read More →