C
Carey Abercrombie
Can you take first/last names in a column and automatically format them to
initials for redacting purposes?
initials for redacting purposes?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
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
=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
R
Ron Rosenfeld
On Thu, 23 Feb 2006 08:25:16 -0800, "Carey Abercrombie" <Carey
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")®EX.MID(A1,"\w(?=\S*$)")
--ron
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")®EX.MID(A1,"\w(?=\S*$)")
--ron
R
Ron Rosenfeld
On Thu, 23 Feb 2006 08:25:16 -0800, "Carey Abercrombie" <Carey
Sorry, small error in previous formula. Should be:
=REGEX.MID(I1,"\b\w")®EX.MID(I1,"\b\w",REGEX.COUNT(I1,"\b\w+"))
--ron
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")®EX.MID(I1,"\b\w",REGEX.COUNT(I1,"\b\w+"))
--ron
C
Carey Abercrombie
PERFECT. EXACTLY WHAT I NEEDED.
THANKS,
CAREY
THANKS,
CAREY
Ron Rosenfeld said:On Thu, 23 Feb 2006 08:25:16 -0800, "Carey Abercrombie" <Carey
Sorry, small error in previous formula. Should be:
=REGEX.MID(I1,"\b\w")®EX.MID(I1,"\b\w",REGEX.COUNT(I1,"\b\w+"))
--ron
C
Carey Abercrombie
WOW, THANKS VERY MUCH. EXACTLY WHAT I NEEDED.
CAREY
CAREY