Strip out text/numbers

[Celticshadow]

Hi All

I require to strip out the numbers either side of the hyphen as below. I
then wish to place the numbers on the left of the hyphen in a separate cell
and the numbers on the right of hyphen in another separate cell.

Before
Column F
1-9
1-35
12-200
100-200
100-1001

The layout may be jumbled up and not as above for example column may look
like this

100-1001
1-9
12-200
100-1001
1-35

But I would like a result as below if possible.

After

Column G
1
1
12
100
100

Column H
9
35
200
200
1001

Many thanks in advance.

Kind Regards

Celticshadow

 

[Joe User]

I require to strip out the numbers either side of the hyphen as below.

In Excel 2003, use Data > Text To Columns.


----- original message -----

 

[Pete_UK]

If you want a formula solution for this, you can put this in G1:

=--LEFT(F1,SEARCH("-",F1)-1)

and this in H1:

=--RIGHT(F1,LEN(F1)-SEARCH("-",F1))

and then copy these down as far as required. The -- converts the text
values from LEFT and RIGHT into numeric values, but if text values are
okay for you then just leave the -- out.

You can fix the values and then sort each column separately if you
want the numbers in that order.

Hope this helps.

Pete

 

[Gary''s Student]

See Text to Columns in Excel Help