Extract text

[Celticshadow]

Hi All

I have some text as below:

10-1
9-12
10-10

I need to be able to extract the numbers to the left of the dash and insert
them in a separate column and also to extract the numbers to the right of the
dash and place them in a separate column.

Column
A B
10 1
9 12
10 10

Any pointers would be much appreciated.

Kind Regards

Celticshadow

 

[T. Valko]

Try using Data>Text to columns

Make sure there is an empty column to the immediate right of your data.

Select your data
Goto the menu Data>Text to Columns
Delimited>Next
Check Other and enter a dash in the little box
Finish

 

[Celticshadow]

Hi

That does work but it is not quite what I am after. The column with the data
in could run to 40 rows and thus I require to run this as a formula all the
way down an adjacent column/s as below.

Column
A B D
10-1 10 1
9-12 9 12
10-10 10 10
8- 7 8 7

Many thanks

Celticshadow

 

[T. Valko]

OK, try these:

For column B...

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

For column D...

=--MID(A1,FIND("-",A1)+1,20)

Note that these formulas will return the values as NUMBERS. If any of your
entries in column A have leading zeros, 001-05, then these leading zeros
will be stripped off. If that's the case and you need to retain those
leading zeros then just remove the double dashes from each formula.

 

[Celticshadow]

Hi again

What a swift response. That works an absolute treat. Many thanks for your
help it really is appreciated, a credit to the forum.

Thanks again

Celticshadow

 

[T. Valko]

You're welcome. Thanks for the feedback!