Split cell into multiple columns with different length

Hi All,

Desperate need your help here as I don't know what the formula or vb script for this.
I have these data in 1 cell (column A2) and I need to split each of them this into 6 columns

Col A2
Phone (n/a) for 123456 (work) to be delete.
Col A3
Phone (1234 ex 1) for 237 (home) to be delete.
Col A4
Phone (_02 354) for 56 (fax) to be delete.

Need to split them into 6 columns by space but there is space in the bracket.
With the space in the bracket, this is will be ignored and need the whole data inside the bracket & include the bracket e.g. (_02 354)

Looking for these following result
Col B2 Phone - this is the same for all cells
Col C2 (n/a) - this data always in bracket and comes in different length & type
Col D2 for - this is the same for all cells
Col E2 123456 - always number but in different length
Col F2 (work) - this data always in bracket and text, comes in different length
Col G2 to be deleted. - this is the same for all cells

Col B3 Phone
Col C3 (1234 ex 1)
Col D3 for for
Col E3 237
Col F3 (home)
Col G3 to be deleted.

The problem is I can't use the excel built-in function convert text or delimited to split this data
Can anyone help with the simple formula or macro vbscript?
Let me know if it's not clear and thanks a lot guys.

will you please upload a sample file which have minimum 10 rows... data

so we can do something for you....

Regards
CA Mahaveer Somani

Try this

Please Login or Register  to view this content.

Mahaveer - thanks for looking at it and Jindon, this code works like magic.
Thanks a lot for this :D
Really appreciate for your quick respond.