Tags: MID

Convert a text entry into its number equivalent

{ 0 Comments }

Consider this simple two column table showing text entries in column A and the corresponding numbers in column.  Assume this data is in range A2:B11 (headings are in A1:B1). text Value A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 0 The objective is to […]

Read More →

Remove special characters from a string

{ 4 Comments }

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

{ 51 Comments }

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 →

Sum data from a particular cell of last n sheets only

{ 0 Comments }

A number is entered in cell A1 of every sheet in a multi sheet workbook.  The task is to sum numbers in cell C2 of the Summary sheet from cell A1 of last x sheets – x is the number of sheets (mentioned in cell B2 of the Summary sheet) from which numbers have to be summed […]

Read More →

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

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

Read More →

Extract specific number of characters from an alphanumeric string without breaking any word

{ 4 Comments }

When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell.  Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell.  Furthermore, when splitting data into multiple cells, it should […]

Read More →

Generate a list of all tabs names without using VBA

{ 252 Comments }

Assume an MS Excel file has 4 worksheets – Sheet1, Sheet2, Sheet3 and Sheet4.  Insert a sheet before Sheet1 and name that tab as Summary.  On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards.  Furthermore, the sheet names so generated, should be dynamic for the following changes: 1. Sheets […]

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 →

Split data into multiple tabs

{ 318 Comments }

Assume a four column database with department as the second column.  Entries in the department column may be repeated.  A person may wish to create one worksheet tab for each department.  Therefore, as and when a person names a worksheet tab as one of the departments, all rows of that department should appear on this new sheet. You […]

Read More →