Tags: TRIM

Extract City, State and Pin code from an address string

{40 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.  Some other variations could be:

1. End the address with Contact Numbers
2. There are no spaces between City, State and Pin code

There are of course many other variations which are possible and the sheer number of these variations makes it difficult to list down all of them.

Here is an example of address strings from where the State, City and Pin code need to extracted in three columns:

1. F-45, Pocket 1, Sector 198, Noida - 201303, Uttar Pradesh, India
2. S-45/B, Pocket 1-C, Phase II, Sector 189, Gurgaon, Haryana - 122002
3. RR-45/B, Pocket II-S, Cross 2, Noida - 201303, India
4. T-45, Sector 198, Lucknow - 226001, Uttar Pradesh, India
5. V-45(A), Sector 193C,Allahabad-211002 Uttar Pradesh India
6. V-45(A), Sector 193C,Allahabad-211002Uttar PradeshIndia

You may refer to my solution in this workbook.

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

{2 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 be ensured that no word gets split/broken.  So if the 50th character is alphabet k of the word Like, then that cell should hold words till the word which appears just before Like so that the length does not exceed 50 characters.

You may refer to my solution in the attached 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.

To remove only special characters appearing at the beginning and end, you may refer to my solution at the following link.

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.