Formula to find 2nd number in a cell

[j8mie]

Hope someone might be able to help me with a formula that's been doing my head in for a few of days now.

Basically I would like to split the contents of a cell into separate parts without using Text to Columns.

The data is always in the following format, word, space, number (0-9), hyphen, number (0-9), space, word

eg
Castle 5-7 Fort
Temple 1-0 House
Cathedral 0-4 Church

I've had no problems getting the first word, first number and last word into new cells. But it's that second number after the hyphen which seems to be impossible to get into a cell on it's own.

Can anyone help?

 

[tigeravatar]

j8mie,

If the second number is always only one digit, you can use the following:
=MID(A1,FIND("-",A1)+1,1)

~tigeravatar

 

[machopicho]

=mid(a1,find("-",a1)+1,1)

 

[CharlesChuckieCharles]

=VALUE(MID(A1,IFERROR(FIND("-",A1,1),0)+1,(SEARCH(" ",A1,IFERROR(FIND("-",A1,1),0))-IFERROR(FIND("-",A1,1),0))))

finds the value regardless of length (assuming a space always follows the second number)

 

[j8mie]

Thank you so much everyone. Those formulas worked like a charm.

I can't believe I was so close. I had =mid(a1,find("-",a1),1). Leaving out the +1 meant I kept getting "-".

Thanks once again

 

[Peter_SSs]

Welcome to the MrExcel board!

If you actually wanted the number (as opposed to a text value), you could modify it to ..
=-MID(A1,FIND("-",A1),2)

 

[j8mie]

Just run into another problem. Turns out some of the words might have a space in them

eg
Castle 5-7 Fort William
Temple 1-0 House
Cathedral Tower 0-4 Church

The formula I was using for the first word was.
=IF(ISERR(FIND(" ",B3)),B3,LEFT(B3,FIND(" ",B3)))
This would only pick up Castle, Temple and Cathedral from the list above.

And this is the formula I used to pick out the second word
=RIGHT(B3,LEN(B3)-FIND("*",SUBSTITUTE(B3," ","*",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))
This would only pick up William, House and Church from the list above.

Any thoughts how to pick up all the text before the first number, and all the text after the second number. There will always be a space between the numbers and text if that makes it any easier.

 

[Peter_SSs]

Assuming still the numbers are single-digit and no spaces in the 'digit-digit' section, try these.

Excel Workbook
	A	B	C
1
2	Castle 5-7 Fort	Castle	Fort
3	Temple 1-0 House	Temple	House
4	Cathedral 0-4 Church	Cathedral	Church
5	Castle 5-7 Fort William	Castle	Fort William
6	Temple 1-0 House	Temple	House
7	Cathedral Tower 0-4 Church	Cathedral Tower	Church
Extract Number

 

[Ingolf]

Hi,

Try this: =LEFT(A1,FIND("-",A1)-2) for word from left side and =RIGHT(A1,LEN(A1)-(FIND("-",A1)+2)) for word in right side

 

[j8mie]

Thank you once again. Both of those formulas do the job perfectly.

This site has proved very useful in such a short space of time. Thank you for taking the time to help me out.