Remove special characters from a string


Hi, Assume a column of names as follows: Name Mohammed Zia-Ul Haque Steven Thomas – ,-Rohit Sunil Ahir-Chowdhary.- Anuj ———– Sameer — ..,Mohit — Rajeev Nair. Monalisa . Das Vijeta … –,.Anjana. M.U..,- Please observe that there are special characters before the name, within the name and after the name.  The task is to remove […]

Read More →

Extract City, State and Pin code from an address string


For a lot of Excel users, a common problem is to extract City, State and Pin Code from an address string.  What compounds the issue is that there is no standardisation in an address string.  For e.g., one may end the address with a Pin code, while others may end it with a State and Country.  […]

Read More →

Remove abbreviations appearing before a name


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

Read More →

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


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

Read More →

Count entries in a range which exclude certain user defined words


Assume the following sentences in range B2:B6 B2 – This is an Apple Pie B3 – An apple a day keeps a doctor away B4 – These Pears, apples and mangoes are sweet B5 – In this season, prices of mangoes have increased B6 – This is a glass of Guava juice In range B9:B10, type […]

Read More →

Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook


Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions – .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start […]

Read More →

SUMPRODUCT function to work on a range with interspersed text values


In a scenario where text values are interspersed with numeric data columns, the usual SUMPRODUCT function will not work.  One will have to use a combination of array formulas and the SUMPRODUCT function. You may refer to my solution in this workbook.

Read More →