Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I get the first letter of a word in excel
I have this problem, see I want to create a script, I have the following information available to me. First name: John MI: C Last name: Doe Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also I have similar problems to with dual last names, like dela toya, I have to convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!! |
#2
|
|||
|
|||
=left("john",1)&"cdoe"
if john is in A1 you can use A1 instead of "john" Shadowofthedarkgod wrote in message ... I have this problem, see I want to create a script, I have the following information available to me. First name: John MI: C Last name: Doe Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also I have similar problems to with dual last names, like dela toya, I have to convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!! |
#3
|
|||
|
|||
Hi
There are no exact naming standards, so there is no bomb proof way to parse names correctly. But if you have Outlook on your system, try this one: http://www.dicks-blog.com/archives/2...-with-outlook/ HTH. Best wishes Harald "Shadowofthedarkgod" skrev i melding ... I have this problem, see I want to create a script, I have the following information available to me. First name: John MI: C Last name: Doe Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also I have similar problems to with dual last names, like dela toya, I have to convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!! |
#4
|
|||
|
|||
Sorry for clicking Send too fast. You may also need:
=LEFT(A1,1) returns the first letter from A1. =LOWER(A1) converts to lowercase. =SUBSTITUTE(A1," ","") removes spaces. HTH. Best wishes Harald "Shadowofthedarkgod" skrev i melding ... I have this problem, see I want to create a script, I have the following information available to me. First name: John MI: C Last name: Doe Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also I have similar problems to with dual last names, like dela toya, I have to convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!! |
#5
|
|||
|
|||
From a quick search of the tips "Remove Space" Problem: The text in range B13:16 contains redundant parentheses. How could we quickly remove them and set the format of the cells to ""general""?" Solution: In cell D13 enter the following formula: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(B13,"")"","""),"" (""," "")),""General") Copy the formula to cells D14:16 and then copy-paste special D13:16 as values into B13:16. Originial Text______Result (Excel)_____________Excel (Power point)_______Power point (Access)____________Access (Outlook)___________Outlook The Formula: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(B13,")",""),"("," ")),"General") Will give you a good start -- Maurice ------------------------------------------------------------------------ Maurice's Profile: http://www.excelforum.com/member.php...fo&userid=1948 View this thread: http://www.excelforum.com/showthread...hreadid=375006 |
#6
|
|||
|
|||
Lets say you have the 3 parts of the name in cells A1, B1 and C1. Then enter the following formula in cell D1 =LEFT(A1,1)&LEFT(B1,1)&SUBSTITUTE(C1," ","") Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=375006 |
#7
|
|||
|
|||
This doesn't account for every possibility, but does take care of the
examples given ... plus a couple not mentioned: =IF(ISERR(FIND(". ",A1)),LEFT(A1)&MID(SUBSTITUTE(A1," ",""),FIND(" ",A1),25),LEFT(A1)&MID(SUBSTITUTE(SUBSTITUTE(A 1,". ","")," ",""),FIND(" ",A1),25)) Watch out for word-wrap! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shadowofthedarkgod" wrote in message ... I have this problem, see I want to create a script, I have the following information available to me. First name: John MI: C Last name: Doe Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also I have similar problems to with dual last names, like dela toya, I have to convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!! |
#8
|
|||
|
|||
Here is one way.
Enter this code in a standard code module '--------------------------------------------------------------------- Private Function RESubString(Inp As String, _ Pattern As String, _ Optional N As Long = 0) As String '--------------------------------------------------------------------- Dim oRegExp As Object, m As Object On Error GoTo RE_error Set oRegExp = CreateObject("VBScript.RegExp") oRegExp.Pattern = Pattern oRegExp.Global = True Set m = oRegExp.Execute(Inp) RESubString = IIf(m.Count 0, m(N).Value, "") GoTo RE_Exit RE_error: RESubString = "RE Error" RE_Exit: Set oRegExp = Nothing On Error GoTo 0 End Function '--------------------------------------------------------------------- Public Function ConvertName(nme As String) '--------------------------------------------------------------------- ' Function: Extracts the last name from a names string, and the other ' first letters '--------------------------------------------------------------------- Dim sRegExp As String Dim arynames Dim sLastName As String Dim sFirtsNames As String Dim sTemp As String Dim i As Long sRegExp = "\b([a-z]+\s+)*[A-Z](\w+\S?)*([-'][A-Z](\w+\S?)*)?\b(?=(\s+([JS]r\.?|[IVX]+) )?\s*$|,)" sLastName = Replace(RESubString(nme, sREgExp), " ", "") sFirstnames = Left(nme, Len(nme) - Len(sLastName)) arynames = Split(sFirstnames, " ") For i = LBound(arynames) To UBound(arynames) sTemp = sTemp & Left(arynames(i), 1) Next i ConvertName = LCase(sTemp & sLastName) End Function and then use in the worksheet like so =ConvertName(A1) -- HTH Bob Phillips "Shadowofthedarkgod" wrote in message ... I have this problem, see I want to create a script, I have the following information available to me. First name: John MI: C Last name: Doe Now this is what I want; I want to convert this John C. Doe to "jcdoe". Also I have similar problems to with dual last names, like dela toya, I have to convert from juan r. dela toya to jrdelatoya. How do I do that? Thanks!! |
#9
|
|||
|
|||
Thanks for all the reply. However, I forgot to add how about dual names or even three names? Let's say, michelle carmen rose c. peters, it has to be mcrcpeters. Thanks for the dual last names reply! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Entering Excel information into MS Word | Excel Discussion (Misc queries) | |||
Column format when merging Excel data to Word labels | Excel Discussion (Misc queries) | |||
Installed Word XP only, then applied Office SP3, then Excel - need 2 re-update? | Excel Discussion (Misc queries) | |||
getting specific info from a word document into excel | Excel Discussion (Misc queries) |