Search code examples
excelseparator

How to split value of one cell into multiple cells in Excel?

I have Name and Address column in excel looks like a

Data

I want to take City name and Pincode in separate columns like 

Secunderabad   500094
warangal       506005
warangal       506005

Is it possible to do in excel? or is there any option for this please let me know

Solution

  • If all are separated with a hyphen, "-", then you could use the Left and Right functions as follows:

    =LEFT(A1, FIND("-",A1) - 1)

    and

    =RIGHT(A1, LEN(A1) - FIND("-",A1))

    where A1 is the cell with city and code together, and the Left formula gets the city and the Right formula gets the code.

    EDIT

    Assuming each cell has CITY- and PHNO- to set off the positions of each, then you could do something like the following.

    Get city name:

    =TRIM(MID(A1, FIND("CITY-",A1) + 5, FIND("-", A1, FIND("CITY-",A1) + 5) - FIND("CITY-",A1) - 5))

    Get code:

    =TRIM(MID(A1, FIND("-", A1, FIND("CITY-",A1) + 5) + 1, FIND("PHNO-",A1) - FIND("-", A1, FIND("CITY-",A1) + 5) - 1))

    Get phone number:

    =RIGHT(A1, LEN(A1) - FIND("PHNO-",A1) - 4)

    If a cell doesn't contain a PHNO- or a CITY-, then the formulas will not work.