Picking data out of text

[Aussie_Striker]

I am trying to pull a single word out of a line of text. The word varies in length which is why I am having the problem.

example
Bowling: proficient Consistency: respectable

I want to pull the data 'proficient' and 'respectable' to two different cells. The word will vary in those positions though and change in length. Is there a formula that will get it irrespective of the length of the word?

[Bryan Hessey]

I am trying to pull a single word out of a line of text. The word varies in length which is why I am having the problem.

example
Bowling: proficient Consistency: respectable

I want to pull the data 'proficient' and 'respectable' to two different cells. The word will vary in those positions though and change in length. Is there a formula that will get it irrespective of the length of the word?

Hi,

something like

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50,50))

and

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),150,50))

should do the trick, increase the Rept etc as required.

hth
---

[Aussie_Striker]

Thanks, that works. I don't understand how it works exactly but it works.

[Bryan Hessey]

Thanks, that works. I don't understand how it works exactly but it works.

it makes BIG spaces and picks words out from somewhere in there, then trims off the spaces.

---