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?

 

[Peter_SSs]

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.

Glad to help out and thanks for the feedback.

When you say that both formulas "do the job perfectly", I'm not sure if you meant all four formulas suggested by Ingolf and me or just what your requirements are, but note that our formulas do not all produce the same result.

It may not matter to you, but Ingolf's formula for the left word(s) leaves a space on the end so for example your get "Castle " not "Castle". If you are certain that there is only ever one (and not multiple) space between the first word(s) and the first number then you could just use
=LEFT(A2,FIND("-",A2)-3)
I used TRIM instead since it caters for the case where there may be 2 or more spaces between the word(s) and the number.

If there is only one space between the second number and the final words then our formulas produce the same result. If there happened to be more than one space a similar issue would arise.