pulling text from the middle of a string

**DRFILL** · 09-21-2012, 10:34 AM

simple question for you here. I have three examples of text that will be in the cells.

Lloyd 8REC-60YDS 11PTS
Olsen 1REC-13YDS 1PT
Bironas 23YD FG 1XP 4PTS

I need to pull out just the number of points that each player scored. I currently have it set up with 2 helper cells the first cell pulls out the 5 characters from the right. leaving

11PTS
(space)(space)1PT
(space)4PTS

the second helper cell pulls out the 2 characters from the left. I then take the value of this second cell to get my number. As you can tell whenever someone scores only 1PT I am going to run into issues because the second helper is just pulling out the spaces. I tried to take the value of the first helper cell with no luck.
Any Ideas?
Thanks

**benishiryo** · 09-21-2012, 10:37 AM

hi DRFILL. assuming data is in A1, how about:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))

**Sean Thomas** · 09-21-2012, 11:30 AM

If A1 is your text then
=TRIM(RIGHT(A1,5))

this will use the 5 characters on the right, and then delete any spaces.
ie 11pts or 1pts

your second example -does that have 2 spaces? if not you will get "S 1PT"

As long as the PTS is in upper case and is PT or PTS

you could use =TRIM(MID(A1,FIND("PT",A1)-2,5))
this will find PT then pull the string out starting 2 charachters to the left. And in the case of only 1 number it will remove the space.

Hope this helps
Sean