software

Can you take first/last names in a column and automatically format them to
initials for redacting purposes?

Assuming the First and Last names are separated by a quot;spacequot;.....

=LEFT(A1,1)amp;MID(A1,FIND(quot; quot;,A1,1) 1,1) will give you the first character
of each name......

=quot;?quot;amp;MID(A1,2,FIND(quot; quot;,A1,1)-1)amp;quot;?quot;amp;MID(A1,FIND(quot; quot;,A1,1) 2,99) will return
both names separated by a space, but with the first character of each name
replaced with a question mark.

Vaya con Dios,
Chuck, CABGx3
Vaya con Dios,
Chuck, CABGx3
quot;Carey Abercrombiequot; wrote:

gt; Can you take first/last names in a column and automatically format them to
gt; initials for redacting purposes?

On Thu, 23 Feb 2006 08:25:16 -0800, quot;Carey Abercrombiequot; lt;Carey
gt; wrote:

gt;Can you take first/last names in a column and automatically format them to
gt;initials for redacting purposes?

You cannot do this with formatting, but you can use a function.

Is that all that is in the column? Are there any middle names, titles or
suffixes?

The following will return the first letter of the first word in the cell,
followed by the first letter of the last word in the cell. So it will handle
cells containing just first/last or first/middle/last.

An initial followed by a space or a dot will be treated as a word. In other
words quot;J. R. Jonesquot; would return quot;JJquot;

1. Download and install Longre's free morefunc.xll add-in from
xcell05.free.fr/

Note that if you install with all options, there will be a menu entry (tools
menu) to allow you to distribute it with the workbook).

2. With your data in A1, use the following formula:

=REGEX.MID(A1,quot;^\wquot;)amp;REGEX.MID(A1,quot;\w(?=\S*$)quot;)--ron

On Thu, 23 Feb 2006 08:25:16 -0800, quot;Carey Abercrombiequot; lt;Carey
gt; wrote:

gt;Can you take first/last names in a column and automatically format them to
gt;initials for redacting purposes?

Sorry, small error in previous formula. Should be:

=REGEX.MID(I1,quot;\b\wquot;)amp;REGEX.MID(I1,quot;\b\wquot;,REGEX.CO UNT(I1,quot;\b\w quot;))--ron

WOW, THANKS VERY MUCH. EXACTLY WHAT I NEEDED.
CAREY

quot;CLRquot; wrote:

gt; Assuming the First and Last names are separated by a quot;spacequot;.....
gt;
gt; =LEFT(A1,1)amp;MID(A1,FIND(quot; quot;,A1,1) 1,1) will give you the first character
gt; of each name......
gt;
gt; =quot;?quot;amp;MID(A1,2,FIND(quot; quot;,A1,1)-1)amp;quot;?quot;amp;MID(A1,FIND(quot; quot;,A1,1) 2,99) will return
gt; both names separated by a space, but with the first character of each name
gt; replaced with a question mark.
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Carey Abercrombiequot; wrote:
gt;
gt; gt; Can you take first/last names in a column and automatically format them to
gt; gt; initials for redacting purposes?

PERFECT. EXACTLY WHAT I NEEDED.
THANKS,
CAREY

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 23 Feb 2006 08:25:16 -0800, quot;Carey Abercrombiequot; lt;Carey
gt; gt; wrote:
gt;
gt; gt;Can you take first/last names in a column and automatically format them to
gt; gt;initials for redacting purposes?
gt;
gt; Sorry, small error in previous formula. Should be:
gt;
gt; =REGEX.MID(I1,quot;\b\wquot;)amp;REGEX.MID(I1,quot;\b\wquot;,REGEX.CO UNT(I1,quot;\b\w quot;))
gt;
gt;
gt; --ron
gt;