pick data from right after first space from right ends until the next space

[Noman050]

Hi all,

would like to have a formula where excel formula pick data from right after first space from right ends until the next space as shown in the attached file column B

[WideBoyDixon]

Might be a long-winded way but it should work:

Please Login or Register  to view this content.

WBD

[Glenn Kennedy]

or:

=--MID(A1,FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,4)


or:

=FILTERXML("<A><B>"&SUBSTITUTE(A1," ","</B><B>")&"</B></A>","//B[position()="&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))&"]")

[Noman050]

working but doesnt work if there a text instead number.

[WideBoyDixon]

Take the "+0" off the end if it's not guaranteed to be a number.

WBD

[Glenn Kennedy]

1. Who are you talking to?

2. If there can be text there, why did your sample file only contain numbers?

[Noman050]

Thanks it worked

[WideBoyDixon]

Also, Glenn's second suggestion is very cool and clever. You could even shorten it a little:

Please Login or Register  to view this content.

WBD

[Glenn Kennedy]

Norman. To avoid confusion, please say who you're talking to when replying to threads if more than 1 person is helping you.