Split numbers in a cell to separate cells

[uncleslinky]

Hi guys,


I have a multiple numbers in a cell which I need to split into seperate cells.


For example, in A1 I have: 4-1 (0-1)


I would like to split this so each number is in it's own cell...


B1 - 4
C1 - 1
D1 - 0
E1 - 1


In this example each number is a single digit however numbers may sometimes be two digits and therefore I wasn't able to find a simple way using just 'left' or 'right'.


I've been experimenting with "find" to locate the position of "-" and "(" but so far I'm a little stumped on what to do next. To extract the first number I've come up with:


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


This seems to extract the number and the -1 removes the hyphen but I'm having trouble working on the other numbers. Any ideas? :D


Thanks

 

[bosco_yip]

B1 :
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("-"&A$1,"(","-"),")",),"-",REPT(" ",30)),ROW(1:1)*30,30))
Copy down

 

[uncleslinky]

B1 :
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("-"&A$1,"(","-"),")",),"-",REPT(" ",30)),ROW(1:1)*30,30))
Copy down

Hi, thanks for the reply

That works as you say by dragging down the sheet but is it possible to have the numbers separated horizontally into B1, C1, D1, E1?

Thanks again

 

[bosco_yip]

B1 :
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("-"&$A1,"(","-"),")",),"-",REPT(" ",30)),COLUMNS($A1:A1)*30,30))
Copy across

 

[uncleslinky]

B1 :
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("-"&$A1,"(","-"),")",),"-",REPT(" ",30)),COLUMNS($A1:A1)*30,30))
Copy across

Brilliant!!
Thanks