Extract text

  • Thread starter Thread starter Celticshadow
  • Start date Start date
C

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
 
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
 
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
 
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.
 
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
 
Back
Top