Extract Number with Values/Text ONLY if number in front and Values/Text ONLY if no number

[bjnockle]

Looking for formula to extract number with values/text ONLY if there is number in front of the values and extract Values/Text ONLY if there is no number in front of the values/text from column I. Desired outcome is in column J. See attached sample.

Thanks

[Shareez Saleem]

Please Login or Register  to view this content.

[bjnockle]

Shareez Saleem: Excellent solution, Shareez. Thanks a lot.

[johnmpl]

Hi to both!

Another solution could be:
=TRIM(LEFT(SUBSTITUTE(I2," - ",REPT(" ",99),LEN(I2)-LEN(SUBSTITUTE(I2,"-",""))),99))

Check file. Blessings!