• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Kindly Help

R

rahulsharma012

New Member
Hi,


these are two data in two cells.all i want that Mr anil mehta name should be separated with mobile number and email id in 3 different columns.

kindly suggest a formula.

[pre]
Code: 
Mr. Anil Mehta919829042971udaipur@arihantbearing.com 

Mr. H.S. Bhandari / Rajiv Bhandari9414126710etepl@rediffmail.com
[/pre]
 
Rahulsharma012


If the data is as you posted there is no easy/simple way to do what you want


If you extract all though numbers to a second column suing, using the technique shown here: http://chandoo.org/forums/topic/extract-numbers


Then you can use that to work out the left and right components to get the Name and Email address


If your data is in A2:A3


B2: =LEFT(A2,FIND(C2,A2)-1)


C2: =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),0),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)


D2: =RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2))


Worked example here: https://www.dropbox.com/s/fc9hlmy12zv04zf/Seperate%20Names.xlsx
 
Hi Hui,


i have Mr. Anil Mehta919829042971udaipur@arihantbearing.com in one cell and Mr. H.S. Bhandari / Rajiv Bhandari9414126710etepl@rediffmail.com in second cell in the same column likewise 100 details with name,mobile number and email id.


just want name ,mobile number and email id to get separated in 3 different columns horizontally.


thanks,

Rahul
 
Hi rahulsharma012


Assuming that your data is as follows:

In column A:

A1: Mr. Anil Mehta919829042971udaipur@arihantbearing.com

A2: Mr. H.S. Bhandari / RajivBhandari9414126710etepl@rediffmail.com


Then in the following cells & copy down.

B1: =LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)

C1: =LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

D1: =SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,"")


Note: B2 = Mr. H.S. Bhandari / RajivBhandari as there is no easy solution, This can be copied as a value and edited where applicable!


Kevin
 
You must log in or register to reply here.
Back
Top