Excel: Formula to split words from a cell


Hi, I have a set of associates data which are placed in excel under single cell. I need that data to be split into 8 cells. Please find below example. My data

A1
123456 James Vasanth (00) 123-456 (00) 789-101 JamesBvasanth@mail.com Tech lead "NO 01, 23rd street, Pune"
Output: [TR] [TD="width: 64, align: right"]123456
A1 B1 C1 D1 E1 F1 G1 H1
[TABLE="width: 64"]
[/TD] James Vasanth (00) 123-456 (00) 789-101 JamesBvasantha@mail.com Tech lead "NO 01, 23rd street, Pune" [/TR] [/TABLE] So the entire data in cell A1 should be split in to 8 cells as shown above. The logic which i have is at the start always it will be numbers that should be split in A1. followed by that we have Name that should be split into B1 & C1 which is first & last name. Then we have 2 contact numbers which always start with open bracket ( which should be split into D1 & E1 Then email ID need to be split in F1 Followed by that designation in G1 Then atlast in H1 we will have address and always it starts with " and ends with " Can anyone help me with formula or VBA which will be much helpful. Thanks!!


This question generated 26 answers. To proceed to the answers, click here.

This thread is current as of February 18, 2017.


For more resources for Microsoft Excel