Prev: comparing more then one column with numbers and letters
Next: Range Vlookup
From: Andy on 21 Apr 2010 05:15
I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
As there are many other examples, I need a function that will seek out the
last ' ' in the cell content and then return all characters to the right
hand-side of this ' '.

e.g. 'Mr K Peters' would return 'Peters'

Please let me know if I can clarify!
From: Jacob Skaria on 21 Apr 2010 05:20
Hi Andy

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

--
Jacob (MVP - Excel)


"Andy" wrote:

> I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
> As there are many other examples, I need a function that will seek out the
> last ' ' in the cell content and then return all characters to the right
> hand-side of this ' '.
>
> e.g. 'Mr K Peters' would return 'Peters'
>
> Please let me know if I can clarify!
From: Andy on 21 Apr 2010 05:25
Brilliant, thanks!

"Jacob Skaria" wrote:

> Hi Andy
>
> =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
>
> --
> Jacob (MVP - Excel)
>
>
> "Andy" wrote:
>
> > I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'.
> > As there are many other examples, I need a function that will seek out the
> > last ' ' in the cell content and then return all characters to the right
> > hand-side of this ' '.
> >
> > e.g. 'Mr K Peters' would return 'Peters'
> >
> > Please let me know if I can clarify!
 | 
Pages: 1
Prev: comparing more then one column with numbers and letters
Next: Range Vlookup