software

I have a very large spreadsheet in which column A contains a list of names
e.g. Fred Bloggs. I need only the surname in this column - is there anyway I
can remove the christian name without having to do this individually cell by
cell.

On Thu, 16 Feb 2006 09:27:27 -0800, quot;Dawnquot; gt;
wrote:

gt;I have a very large spreadsheet in which column A contains a list of names
gt;e.g. Fred Bloggs. I need only the surname in this column - is there anyway I
gt;can remove the christian name without having to do this individually cell by
gt;cell.

Yes you can. And the manner depends on exactly how the names are formatted.

If the surname is always the last word in the cell, then it can be extracted
using the formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1,quot; quot;,CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1,quot; quot;,quot;quot;)))) 1,255)

If other formats are possible, you will need to post them here.

In B1 enter the above formula, then copy/drag down as far as needed.--ron

Many thanks - you have saved me an awful lot of time.

Regards
Dawn

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 16 Feb 2006 09:27:27 -0800, quot;Dawnquot; gt;
gt; wrote:
gt;
gt; gt;I have a very large spreadsheet in which column A contains a list of names
gt; gt;e.g. Fred Bloggs. I need only the surname in this column - is there anyway I
gt; gt;can remove the christian name without having to do this individually cell by
gt; gt;cell.
gt;
gt; Yes you can. And the manner depends on exactly how the names are formatted.
gt;
gt; If the surname is always the last word in the cell, then it can be extracted
gt; using the formula:
gt;
gt; =MID(A1,FIND(CHAR(1),SUBSTITUTE(
gt; A1,quot; quot;,CHAR(1),LEN(A1)-LEN(
gt; SUBSTITUTE(A1,quot; quot;,quot;quot;)))) 1,255)
gt;
gt; If other formats are possible, you will need to post them here.
gt;
gt; In B1 enter the above formula, then copy/drag down as far as needed.
gt;
gt;
gt; --ron
gt;

On Fri, 17 Feb 2006 02:50:28 -0800, quot;Dawnquot; gt;
wrote:

gt;Many thanks - you have saved me an awful lot of time.
gt;
gt;Regards
gt;Dawn
gt;

You're welcome. Glad to help. Thanks for the feedback.
--ron

Ron

Could you also let me know the formula to use to so I can extract the
christian name from the column.

Many thanks

Dawn

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 16 Feb 2006 09:27:27 -0800, quot;Dawnquot; gt;
gt; wrote:
gt;
gt; gt;I have a very large spreadsheet in which column A contains a list of names
gt; gt;e.g. Fred Bloggs. I need only the surname in this column - is there anyway I
gt; gt;can remove the christian name without having to do this individually cell by
gt; gt;cell.
gt;
gt; Yes you can. And the manner depends on exactly how the names are formatted.
gt;
gt; If the surname is always the last word in the cell, then it can be extracted
gt; using the formula:
gt;
gt; =MID(A1,FIND(CHAR(1),SUBSTITUTE(
gt; A1,quot; quot;,CHAR(1),LEN(A1)-LEN(
gt; SUBSTITUTE(A1,quot; quot;,quot;quot;)))) 1,255)
gt;
gt; If other formats are possible, you will need to post them here.
gt;
gt; In B1 enter the above formula, then copy/drag down as far as needed.
gt;
gt;
gt; --ron
gt;

On Mon, 20 Feb 2006 02:31:34 -0800, quot;Dawnquot; gt;
wrote:

gt;Ron
gt;
gt;Could you also let me know the formula to use to so I can extract the
gt;christian name from the column.
gt;
gt;Many thanks
gt;
gt;Dawn

Try this:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(
A1,quot; quot;,CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1,quot; quot;,quot;quot;))))-1)--ron

Ron

Again, many thanks - that also worked.

Regards
Dawn

quot;Ron Rosenfeldquot; wrote:

gt; On Mon, 20 Feb 2006 02:31:34 -0800, quot;Dawnquot; gt;
gt; wrote:
gt;
gt; gt;Ron
gt; gt;
gt; gt;Could you also let me know the formula to use to so I can extract the
gt; gt;christian name from the column.
gt; gt;
gt; gt;Many thanks
gt; gt;
gt; gt;Dawn
gt;
gt; Try this:
gt;
gt; =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(
gt; A1,quot; quot;,CHAR(1),LEN(A1)-LEN(
gt; SUBSTITUTE(A1,quot; quot;,quot;quot;))))-1)
gt;
gt;
gt; --ron
gt;