Extract Specific Info from contents of Cell

[RIZVI]

Hi,

I have all the contact info of a customer given in one cell. that is names, address, telephone numbers mobile numbers, e-mails etc.

1. I need to extract in adjacent cell separately a) Names & b) Mobile & telephone numbers. That is in one adjacent cell names come and in another the mobile / telephone numbers.

Names will precede with suffice like Mr. or Ms.; telephone numbers will be 7 digit or 6 digit and mobile numbers will be 10 digits.

Confusion is possible as the contents also has PIN Code / Zip Code, Street Numbers, House numbers etc.

Sample data will be like this: Mr.Ameet, Mr.Neelam Veer, 0832-6683134, Ms.Sankesh Prabhugaonkar +918368326137, 9823025285 Neelam.Veer@albea-group.com , ameet.naik@albea-group.com

1. Desired results shall be: In Once Cell: Mr.Ameet, Mr.Neelam, Ms.Sankesh Prabhugaonkar
2. In Another Cell: 6683134, 6683137, 9823025285

RGDS,

Rizvi.M.H.

 

[oldbrewer]

is there always a comma after the name(s) before phone numbers?

 

[RIZVI]

Hi oldbrewer

Yes there is ALWAYS a comma.

Also what I missed out giving in example was that there may be other details like street name, number, Zim Code, Pin Code, etc like: M/s.Prakash Boxes, 2 Cross, 2nd Street, 4th Floor, St.Inez, Kundaim Indl Area, Goa - 403012

RGDS,

Rizvi.M.H.

 

[energman58]

Assuming that names are always starting Mr. or Ms.

This will produce the name from data in Cell A5

=MID(A5,IFERROR(FIND("Mr.",A5),FIND("Ms.",A5)),FIND(" ",A5,IFERROR(FIND("Mr.",A5),FIND("Ms.",A5))))

Assuming all phone numbers start 0832 or +918

This returns the phone number in A5

=IFERROR(MID(A5,SEARCH("+918",A5),13),MID(A5,SEARCH(A5,"0832"),12))

 

[RIZVI]

Hi energman58

I am facing following issues:

1. If there is no name or telephone number then I get error #VALUE!
2. If there are more than one name and number then it picks up only first name and number.

RGDS,

Rizvi.M.H.

 

[energman58]

Hi energman58

I am facing following issues:

1. If there is no name or telephone number then I get error #VALUE!
2. If there are more than one name and number then it picks up only first name and number.

RGDS,

Rizvi.M.H.

On the first one you need to wrap another iferror around it all:

=IFERROR(MID(A5,IFERROR(FIND("Mr.",A5),FIND("Ms.",A5)),FIND(" ",A5,IFERROR(FIND("Mr.",A5),FIND("Ms.",A5)))),"No Name")

and

=iferror(IFERROR(MID(A5,SEARCH("+918",A5),13),MID(A5,SEARCH(A5,"0832"),12)),"No Number")

On the second one I dont see a simple way around it without a lot of VBA - how is the formula supposed to know there are more than one name in a cell and even if it could which one to use? I suggest maybe you look at importing the data again and playing around with how you do it using text to columns so you split teh cells where this happens

 

[RIZVI]

Thats OK with me. I am able to use it with slight changes here and there and it is perfectly OK.

Thanks for your time and effort buddy.

Rgds,

Rizvi.M.H.