Formula to extract numbers between two characters?

[MariaR323]

I have the following data for HS & College and I'd like to extract only the HS Year as shown below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]HS & College[/TD]
[TD]HS Year (Result I want)[/TD]
[/TR]
[TR]
[TD]HS Year: 2001/Univ Year: 2006[/TD]
[TD]2001[/TD]
[/TR]
[TR]
[TD]HS Year:1997/Univ Year:2005[/TD]
[TD]1997[/TD]
[/TR]
</tbody>[/TABLE]

Is there a formula to achieve this? Noting that the original entries in the column HS & College can have a space or not after the first colon.

Any help would be appreciated as I'm brain fried at the moment.

Thanks!
Maria

 

[Rick Rothstein]

If the wording is always the same...

=0+LEFT(TRIM(SUBSTITUTE(A1,"HS Year:","")),4)

 

[MariaR323]

If the wording is always the same...

=0+LEFT(TRIM(SUBSTITUTE(A1,"HS Year:","")),4)

Unfortunately, I just realized there's a lot of older records that the entry format was not the same and looks like the wording will not always be the same and some entries were made abbreviating Year to Yr.

Any way to rework the formula to account for this?

 

[Rick Rothstein]

Unfortunately, I just realized there's a lot of older records that the entry format was not the same and looks like the wording will not always be the same and some entries were made abbreviating Year to Yr.

Any way to rework the formula to account for this?

Give this a try...

=0+RIGHT(LEFT(A1,FIND("/",A1)-1),4)

 

[MariaR323]

Give this a try...

=0+RIGHT(LEFT(A1,FIND("/",A1)-1),4)

Worked! Thank you!!