Home » Forums » Open Source Learning Center » Question and Answer

Hello friends i need a formula in excel to separate the mobile number and zip code into different cells

Submitted by papanihemanth on 1 November, 2015 - 07:04

Here is my sample cell to separate.
Bellow total in one cell so i need a 3 different cells for address, mobile number, zip code.

24 103/1/1/A, LAXMI NARAYANA NAGAR, UPPAL, HYDERABAD, HYDERABAD, TELANGANA,9866256934, 500013

Thanks in advance

‹ How To create a VBA to print all reports from a drop down menu. How to loop through a range of data to remove duplicates ›
»

Try this

Submitted by Max F. on 2 November, 2015 - 22:15.

Hello,

I am not familiar with your county's ZIP/Phone numbers, but these formulas work under the assumption that the Zips are always 6 digits, the phone number is always 10 digits, and there is a space before the zip code and no spaces before and after the phonenumbr (like your above example).

Here is how I have it set up for these formulas to pull exactly as written:

Address:

=LEFT(A2,SEARCH(C2,A2)-2)

 

Mobile:

=LEFT(RIGHT(TRIM(A2),18),10)

Zip:

=RIGHT(TRIM(A2),6)

 

 

 

»