Extract data from Excel cell [closed]

I have data in excel sheet in Column A in format below and want to extract it to different columns

                Column A
XXXXX (YYYYY (ABCDEF) 0000000000 0000000000)
XXXXX (YYYYY 0000000000)

Result Required

  A   |   B   |           C           |    D
XXXXX | YYYYY | 0000000000,0000000000 | ABCDEF
XXXXX | YYYYY | 0000000000,0000000000 |

Want to use one single formula for both the conditions as data cannot be sorted

in B1 place

=TRIM(LEFT(A1,FIND("(",A1)-1))

This formula takes the all the character to the left of the first "(". The TRIM function removes excess space and a few other special characters. You may or may not want to use trim. if you do not decrease -1 to -2 or -3 etc until you have the right number of characters returned with no space at the end.

in C1

=TRIM(MID(A1,FIND("(",A1)+1,FIND("(",A1,FIND("(",A1)+1)-FIND("(",A1)-1))

Little trickier and a lot of repetitiveness that makes it hard to read. basically we find the first "(" and go 1 character to the right of that and start pulling characters. in order to determine the number of characters to pull, the position of the second "(" was found. this was done by finding the first "(" after the first "(".

in D1

=SUBSTITUTE(TRIM(MID(A1,FIND(")",A1)+1,LEN(A1)-FIND(")",A1)-1))," ",",")

Same as what we did in the previous examples in terms of pulling text after finding a starting point of first ")" and the number of character we looked at the length of the string and subtracted the postion of the first ")". Once it was down to numbers separated by a space, substitue command was used to replace the space with a coma.

in E1

=MID(A1,FIND("(",A1,FIND("(",A1)+1)+1,FIND(")",A1)-FIND("(",A1,FIND("(",A1)+1)-1)

This is just a really ugly repeat of what we did before just finding different start and end points to rip the text with.

You need to do the following in order to all you information back into A1. You need to paste value or your formulas will be pointed at all the wrong things and it will generally just look really bad.