|
|
Return cell characters after space
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!
|
|
0
|
|
|
|
Reply
|
 Utf
|
4/21/2010 9:15:01 AM |
|
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!
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/21/2010 9:20:01 AM
|
|
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!
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/21/2010 9:25:01 AM
|
|
|
2 Replies
2156 Views
(page loaded in 0.053 seconds)
Similiar Articles: Return cell characters after space - microsoft.public.excel ...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 ... Test to see if cell contains a space? - microsoft.public.excel ...Test to see if cell contains a space? - microsoft.public.excel ... It'll return a yes, if the cell contains a space. Ideas? Thank you. ... Return cell characters after ... Inserting a space between characters in a cell - microsoft.public ...Can I automatically have a space after the content in an Excel cel ... Return cell characters after space I have a cell with 'Mr J. Smith' and ... test to see if ... Can I automatically have a space after the content in an Excel cel ...Return cell characters after space - microsoft.public.excel ... Can I automatically have a space after the content in an Excel cel ... Test to see if cell contains a space ... Need formula in query to return part of string after blank space ...Return cell characters after space - microsoft.public.excel ... Need formula in query to return part of string after blank space ... Need formula in query to return part ... Logical IF return a cell reference - microsoft.public.excel.misc ...Return cell characters after space - microsoft.public.excel ... Logical IF return a cell reference - microsoft.public.excel.misc ... Return cell characters after space ... Hightlight cells that contain special characters - microsoft ...... space? - microsoft.public.excel ..... that CONTAIN space?? > >For an Example, COLOUMN A1-A1000 contains ... Highlight All Cells In ... Return cell characters after space ... Excel 2007 - Formatting text in cell (character by character ...Return cell characters after space - microsoft.public.excel ... Excel 2007 - Formatting text in cell (character by character ... Return cell characters after space ... Finding unprintable characters in data - microsoft.public.access ...Return cell characters after space - microsoft.public.excel ... Finding unprintable characters in data - microsoft.public.access ... Return cell characters after space ... test to see if string matches with cell content - microsoft.public ...Can I automatically have a space after the content in an Excel cel ..... reference - microsoft.public.excel.misc ... Return cell characters after space I have a cell ... Return cell characters after space - microsoft.public.excel ...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 ... Return cell characters after space - ExcelBanterExcel Worksheet Functions ... I have a cell with 'Mr J. Smith' and want to return in another cell 'Smith'. As there ... Hi Andy =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT ... Excel, returning the value of a cell after a specific number of ...... returning the value of a cell after a specific number of leading characters ... long, then there is a space and ... B7,5) where B7 is the cell reference. How can I return the ... Excel - Return Characters Surrounding Specific Character - I have ...... query, notice how " TR" has a space before but no space after the two characters. ... in A1 and showing the 3 characters before and after, to return the contents of cell B1. Ms-office - Excel - Construct Mega-Formulas Tutorials... it will return a value of 6. But it's the second space you're after, not ... extract 256 characters from cell A1, starting with the first character after the second space ... 7/23/2012 9:34:03 AM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
39 followers. 