extract number before and after

[crapit]

How to extract number before and after hyphen?
i.e numbers at column B are before hyphen, at col C after.hyphen
A B C
14----1 14 1
2-----3 2 3
6-----10 6 10

 

[Shane Devenshire]

Hi,

for text numbers:
=TRIM(LEFT(A1,FIND("-",A1)-1))
for the right side use the array formul
=TRIM(MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="-"))+1,9))

to return number numbers:
=--TRIM(LEFT(A1,FIND("-",A1)-1))
for the right side use the array formul
=--TRIM(MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="-"))+1,9))

to be an array you must enter the formula by pressing Shift+Ctrl+Enter

 

[CurlyDave]

This would work for extracting the first word

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

Extracting the last word, this seems to work

=RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,"-","$",LEN(A1)-LEN(SUBSTITUTE
(A1,"-","")))))

 

[Rick Rothstein]

See inline comments...

This would work for extracting the first word

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

This would probably be better to use...

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

Extracting the last word, this seems to work

=RIGHT(A1,LEN(A1)-FIND("$",SUBSTITUTE(A1,"-","$",LEN(A1)-LEN(
SUBSTITUTE(A1,"-","")))))

Here is a simpler formula to do that...

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

 

[Rick Rothstein]

Select Column A's entries... click Data/Convert To Text on Excel's menu
bar... select "Delimited" at Step 1 and click Next... put a dash in the
empty field next to the "Other" check box, put a check mark in the "Treat
consecutive delimiters as one" check box and click Next... if the active
cell is A1, then put =$B$1 in the Destination field (adjust the row
according to the row of the Active Cell)... click Finish.

 

[crapit]

This is the best answer
Cheers

Select Column A's entries... click Data/Convert To Text on Excel's menu
bar... select "Delimited" at Step 1 and click Next... put a dash in the
empty field next to the "Other" check box, put a check mark in the "Treat
consecutive delimiters as one" check box and click Next... if the active
cell is A1, then put =$B$1 in the Destination field (adjust the row
according to the row of the Active Cell)... click Finish.