Extract Numbers

[Celticshadow]

Hi All

May I ask if the board could help me out with a formula to extract the numbers and put them into columns as below.

Thanks in advance.

Kind Regards

[RANGE=cls:xl2bb-100][XR][XH=cs:4]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][/XR][XR][XH]1[/XH][XD=h:c]Data[/XD][XD=h:c]Result Required[/XD][XD=h:c]Result Required[/XD][/XR][XR][XH]2[/XH][XD=h:c]11-5[/XD][XD=h:c]11[/XD][XD=h:c]5[/XD][/XR][XR][XH]3[/XH][XD=h:c]11-3[/XD][XD=h:c]11[/XD][XD=h:c]3[/XD][/XR][XR][XH]4[/XH][XD=h:c]10-12[/XD][XD=h:c]10[/XD][XD=h:c]12[/XD][/XR][XR][XH]5[/XH][XD=h:c]10-12[/XD][XD=h:c]10[/XD][XD=h:c]12[/XD][/XR][XR][XH]6[/XH][XD=h:c]10-12[/XD][XD=h:c]10[/XD][XD=h:c]12[/XD][/XR][XR][XH]7[/XH][XD=h:c]10-10[/XD][XD=h:c]10[/XD][XD=h:c]10[/XD][/XR][XR][XH]8[/XH][XD=h:c]10-10[/XD][XD=h:c]10[/XD][XD=h:c]10[/XD][/XR][XR][XH]9[/XH][XD=h:c]10-10[/XD][XD=h:c]10[/XD][XD=h:c]10[/XD][/XR][XR][XH]10[/XH][XD=h:c]10-5[/XD][XD=h:c]10[/XD][XD=h:c]5[/XD][/XR][XR][XH]11[/XH][XD=h:c]10-4[/XD][XD=h:c]10[/XD][XD=h:c]4[/XD][/XR][XR][XH]12[/XH][XD=h:c]10-0[/XD][XD=h:c]10[/XD][XD=h:c]0[/XD][/XR][XR][XH]13[/XH][XD=h:c]9-12[/XD][XD=h:c]9[/XD][XD=h:c]12[/XD][/XR][XR][XH]14[/XH][XD=h:c]9-12[/XD][XD=h:c]9[/XD][XD=h:c]12[/XD][/XR][XR][XH]15[/XH][XD=h:c]9-10[/XD][XD=h:c]9[/XD][XD=h:c]10[/XD][/XR][XR][XH]16[/XH][XD=h:c]9-7[/XD][XD=h:c]9[/XD][XD=h:c]7[/XD][/XR][XR][XH]17[/XH][XD=h:c]9-7[/XD][XD=h:c]9[/XD][XD=h:c]7[/XD][/XR][XR][XH]18[/XH][XD=h:l]I need to extract the number or numbers to left of the hyphen and place them in column B and the number to the right of the hyphen into col C.[/XD][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]20[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]21[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]22[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]23[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]24[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]25[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH=cs:4][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[T. Valko]

Hi All

May I ask if the board could help me out with a formula to extract the numbers and put them into columns as below.

Thanks in advance.

Kind Regards

[RANGE=cls:xl2bb-100][XR][XH=cs:4]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][/XR][XR][XH]1[/XH][XD=h:c]Data[/XD][XD=h:c]Result Required[/XD][XD=h:c]Result Required[/XD][/XR][XR][XH]2[/XH][XD=h:c]11-5[/XD][XD=h:c]11[/XD][XD=h:c]5[/XD][/XR][XR][XH]3[/XH][XD=h:c]11-3[/XD][XD=h:c]11[/XD][XD=h:c]3[/XD][/XR][XR][XH]4[/XH][XD=h:c]10-12[/XD][XD=h:c]10[/XD][XD=h:c]12[/XD][/XR][XR][XH]5[/XH][XD=h:c]10-12[/XD][XD=h:c]10[/XD][XD=h:c]12[/XD][/XR][XR][XH]6[/XH][XD=h:c]10-12[/XD][XD=h:c]10[/XD][XD=h:c]12[/XD][/XR][XR][XH]7[/XH][XD=h:c]10-10[/XD][XD=h:c]10[/XD][XD=h:c]10[/XD][/XR][XR][XH]8[/XH][XD=h:c]10-10[/XD][XD=h:c]10[/XD][XD=h:c]10[/XD][/XR][XR][XH]9[/XH][XD=h:c]10-10[/XD][XD=h:c]10[/XD][XD=h:c]10[/XD][/XR][XR][XH]10[/XH][XD=h:c]10-5[/XD][XD=h:c]10[/XD][XD=h:c]5[/XD][/XR][XR][XH]11[/XH][XD=h:c]10-4[/XD][XD=h:c]10[/XD][XD=h:c]4[/XD][/XR][XR][XH]12[/XH][XD=h:c]10-0[/XD][XD=h:c]10[/XD][XD=h:c]0[/XD][/XR][XR][XH]13[/XH][XD=h:c]9-12[/XD][XD=h:c]9[/XD][XD=h:c]12[/XD][/XR][XR][XH]14[/XH][XD=h:c]9-12[/XD][XD=h:c]9[/XD][XD=h:c]12[/XD][/XR][XR][XH]15[/XH][XD=h:c]9-10[/XD][XD=h:c]9[/XD][XD=h:c]10[/XD][/XR][XR][XH]16[/XH][XD=h:c]9-7[/XD][XD=h:c]9[/XD][XD=h:c]7[/XD][/XR][XR][XH]17[/XH][XD=h:c]9-7[/XD][XD=h:c]9[/XD][XD=h:c]7[/XD][/XR][XR][XH]18[/XH][XD=h:l]I need to extract the number or numbers to left of the hyphen and place them in column B and the number to the right of the hyphen into col C.[/XD][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]20[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]21[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]22[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]23[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]24[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]25[/XH][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH=cs:4][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Try these...

B2: =--LEFT(A2,FIND("-",A2)-1)

C2: =--MID(A2,FIND("-",A2)+1,10)

 

[Michael M]

In ell "B2" try

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

copy down as required

Grrrr....didn't see the last bit !!!

 

[mikerickson]

When I type "11-5" into a cell, Excel interprets that as a date. If the entries in column A are dates, try =MONTH(A2) and =DAY(A2) dragged down.

 

[Celticshadow]

Hi T Valko/Michael M/mikerickson

Many thanks to you all for replying. I have taken T Valko's solution which works a treat again a top class service from the board once again. Many many thanks.

Kind Regards

 

[T. Valko]

Hi T Valko/Michael M/mikerickson

Many thanks to you all for replying. I have taken T Valko's solution which works a treat again a top class service from the board once again. Many many thanks.

Kind Regards

You're welcome. Thanks for the feedback!

 

[pgc01]

Hi

Another option, without formulas, is to use Text to Columns.

 

[Celticshadow]

HiAll

I was trying to use the above formula with some other numbers that appear to be formatted as general but all I am getting is a value error any pointers would greatly received. The data on the first cell is 68-313 and I was the initiator of the original query but for some reason this will now not work. I need the 68 in one cell and the 313 in another separate cell, so extract the digits before and after the hypen and place them in two separate cells.

Kind Regards

 

[Michael M]

HAve a go with PGC01's idea, with the hyphen as the seperator.

 

[Celticshadow]

Hi Michael M

I have already tried that and it does not work and is very messy to be honest.

Kind Regards