Formatting Question

[Carey Abercrombie]

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

[CLR]

Assuming the First and Last names are separated by a "space".....

=LEFT(A1,1)&MID(A1,FIND(" ",A1,1)+1,1) will give you the first character
of each name......

="?"&MID(A1,2,FIND(" ",A1,1)-1)&"?"&MID(A1,FIND(" ",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



"Carey Abercrombie" wrote:

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

[Ron Rosenfeld]

On Thu, 23 Feb 2006 08:25:16 -0800, "Carey Abercrombie" <Carey
Abercrombie@discussions.microsoft.com> wrote:

>Can you take first/last names in a column and automatically format them to
>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 "J. R. Jones" would return "JJ"

1. Download and install Longre's free morefunc.xll add-in from
http://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,"^\w")&REGEX.MID(A1,"\w(?=\S*$)")


--ron

[Ron Rosenfeld]

On Thu, 23 Feb 2006 08:25:16 -0800, "Carey Abercrombie" <Carey
Abercrombie@discussions.microsoft.com> wrote:

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

Sorry, small error in previous formula. Should be:

=REGEX.MID(I1,"\b\w")&REGEX.MID(I1,"\b\w",REGEX.COUNT(I1,"\b\w+"))


--ron

[Carey Abercrombie]

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

"CLR" wrote:

> Assuming the First and Last names are separated by a "space".....
>
> =LEFT(A1,1)&MID(A1,FIND(" ",A1,1)+1,1) will give you the first character
> of each name......
>
> ="?"&MID(A1,2,FIND(" ",A1,1)-1)&"?"&MID(A1,FIND(" ",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
>
>
>
> "Carey Abercrombie" wrote:
>
> > Can you take first/last names in a column and automatically format them to
> > initials for redacting purposes?

[Carey Abercrombie]

PERFECT. EXACTLY WHAT I NEEDED.
THANKS,
CAREY

"Ron Rosenfeld" wrote:

> On Thu, 23 Feb 2006 08:25:16 -0800, "Carey Abercrombie" <Carey
> Abercrombie@discussions.microsoft.com> wrote:
>
> >Can you take first/last names in a column and automatically format them to
> >initials for redacting purposes?
>
> Sorry, small error in previous formula. Should be:
>
> =REGEX.MID(I1,"\b\w")&REGEX.MID(I1,"\b\w",REGEX.COUNT(I1,"\b\w+"))
>
>
> --ron
>