Extract numbers to left of :

[sobrien1234]

I have a list of numbers in column 8 as follows:

1:1-5-4
13:8-3-4
44:22-11-11

I want a formula in column B that extracts the numbers to the left of : - so the answers would be
1
13
44

I want a formula in column C that extracts the numbers to the right of : - so the answer would be:
1
8
22

Thanks in advance

 

[Tricepsratops]

I recommend you to format the content of column A as text, my excel went crazy with those numbers of yours otherwise.

Traductor de Formulas - copia.xlsm
	A	B	C
1	1:1-5-4	1	1
2	13:8-3-4	13	8
3	44:22-11-11	44	22
Hoja4

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,SEARCH(":",A1)-1)
C1:C3	C1	=MID(A1,SEARCH(":",A1)+1,SEARCH("-",A1)-SEARCH(":",A1)-1)

Column B looks for ":" and extracts the left.

Column C looks for ":" and "-" and extracts wha tis in between.

 

[Fluff]

Another option

+Fluff New.xlsm
	A	B	C
1
2	1:1-5-4	1	1
3	13:8-3-4	13	8
4	44:22-11-11	44	22
Master

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(A2,SEARCH(":",A2&":")-1)
C2:C4	C2	=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,LEN(B2)+1,""),"-",REPT(" ",100),1),100))

 

[sobrien1234]

I recommend you to format the content of column A as text, my excel went crazy with those numbers of yours otherwise.

Traductor de Formulas - copia.xlsm
	A	B	C
1	1:1-5-4	1	1
2	13:8-3-4	13	8
3	44:22-11-11	44	22
Hoja4

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,SEARCH(":",A1)-1)
C1:C3	C1	=MID(A1,SEARCH(":",A1)+1,SEARCH("-",A1)-SEARCH(":",A1)-1)

Column B looks for ":" and extracts the left.

Column C looks for ":" and "-" and extracts wha tis in between.

Excellent thanks - and thanks for the detailed explanation. Much appreciated.

 

[sobrien1234]

Another option

+Fluff New.xlsm
	A	B	C
1
2	1:1-5-4	1	1
3	13:8-3-4	13	8
4	44:22-11-11	44	22
Master

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(A2,SEARCH(":",A2&":")-1)
C2:C4	C2	=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,LEN(B2)+1,""),"-",REPT(" ",100),1),100))

Thank you - much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback