I need some help separating numbers from text in a data string
here is the general format of the data string
##.##% ABC; ABC ##, ABC ## (##-## A)
1) the numbers after the second and third ABC as well as the numbers in the parentheses can be single digits such as ABC #, ABC # (#-# A) or any combination of double and single digits
2) the second and third ABC can be of various lengths and can also contain multiple words such as:
ABC
ABCD
ABCDEF ABC ABCDEF
etc
I used the text to columns function to separate the string into three cells
A1
##.## ABC
B1
ABC ##
C1
ABC ## (##-## A)
I used the LEFT function to get ##.## into one cell
I used =SUBSTITUTE(B1," ","|",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))) to get ABC|## into a cell and then used the text to columns function again to get ABC into one cell and then ## into another...
I am struggling on separating the last cell which contains ABC ## (##-## A)...I need to get ABC into one call, ## into another cell, the first number in the parentheses into one cell and the second number in the parentheses into another cell
please help
Bookmarks