LEN formula with first/last names

[brooksc29]

Right now I have this formula in a macro for formatting a workbook that will look at a cell with a first and last name and put the two names into two different cells, then only keep the cell with the last name.

How can I change it to keep the first initial of the first name and the last name?

this is the formula:
=IF(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1], "" "", """")) = 2, MID(RC[-1],FIND("" "",RC[-1])+1,FIND("" "",RC[-1],FIND("" "",RC[-1])+1)-(FIND("" "",RC[-1])+1)),RIGHT(RC[-1],LEN(RC[-1])-FIND(""*"",SUBSTITUTE(RC[-1],"" "",""*"",LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],"" "",""""))))))

 

[dfenton21]

If you want to, for example, take Joe Bloggs and get J. Bloggs, the formula below will do that, where Joe Bloggs is in A1

=CONCATENATE(LEFT(A1, 1),". ", RIGHT(A1,(FIND(" ",A1)-1)))

It seems from your post that you are doing unnessessary work. You first put the first name into a cell, only to remove it again.

I hope this helps, and please let me know if you need further assistence.

Damien

 

[jreedich]

Try This
=LEFT(A1, 1)&". "&RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

 

[shg]

Or

=REPLACE(A1, 2, SEARCH(" ", A1) - 2, ".")

 

[brooksc29]

well, the thing is this formula is used to format each page in the workbook and the data on each page is imported from IE. The formula works really well in the macro so I don't want to change the meat of it.

Is there an easy way to apply a piece to it so that the name returning includes the first initial?

I'm not sure if substitute the above responses in for what I have now that it will work correctly...

this is the entire macro

Columns("B:B").Select
Selection.EntireColumn.Insert

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1], "" "", """")) = 2, MID(RC[-1],FIND("" "",RC[-1])+1,FIND("" "",RC[-1],FIND("" "",RC[-1])+1)-(FIND("" "",RC[-1])+1)),RIGHT(RC[-1],LEN(RC[-1])-FIND(""*"",SUBSTITUTE(RC[-1],"" "",""*"",LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],"" "",""""))))))"

Selection.Copy
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Application.CutCopyMode = False

 

[dfenton21]

Hi There,

Is there an easy way to apply a piece to it so that the name returning includes the first initial?

Sorry for ignoring this request, but I believe you are going about this the wrong way. All you are doing is simple string malipulation, so there is no need to use a formula in your VBA code (and that formula is far too complex for the end result). The following code will achieve the desired result where A1 contains the unmodified name, and A2 will contain the modified name.

    Dim myName As String
    Dim myModifiedName As String
    
    myName = Range("A1").Value
    
    myModifiedName = Left(myName, 1) & ". " & Right(myName, InStr(myName, " ") - 1)
    
    Range("A2").Value = myModifiedName

I advise adding a new module, and test the above code to see if you get the result you need.

 

[jreedich]

Question. What level of Excel are you using?

 

[brooksc29]

Question. What level of Excel are you using?

I'm using Excel 2007.

Sorry for ignoring this request, but I believe you are going about this the wrong way. All you are doing is simple string malipulation, so there is no need to use a formula in your VBA code (and that formula is far too complex for the end result). The following code will achieve the desired result where A1 contains the unmodified name, and A2 will contain the modified name.

I'll try your proposed macro, but in reality, I'm only looking for a temporary fix. Some of the names I need to convert have more than one person with the same last name, so that's why I need the first initial. It doesn't always happen that there are multiple people with the same last name, and the macro has been working really well for me, so I don't need to change it.

Thanks for your help and I'll let you know how your macro works.

 

[brooksc29]

Hi There,

Sorry for ignoring this request, but I believe you are going about this the wrong way. All you are doing is simple string malipulation, so there is no need to use a formula in your VBA code (and that formula is far too complex for the end result). The following code will achieve the desired result where A1 contains the unmodified name, and A2 will contain the modified name.

    Dim myName As String
    Dim myModifiedName As String
    
    myName = Range("A1").Value
    
    myModifiedName = Left(myName, 1) & ". " & Right(myName, InStr(myName, " ") - 1)
    
    Range("A2").Value = myModifiedName

I advise adding a new module, and test the above code to see if you get the result you need.

Didn't work at all. VBA kept highlighting this line "myModifiedName = Left(myName, 1) & ". " & Right(myName, InStr(myName, " ") - 1)" and would not move past it.

 

[dfenton21]

Didn't work at all. VBA kept highlighting this line "myModifiedName = Left(myName, 1) & ". " & Right(myName, InStr(myName, " ") - 1)" and would not move past it.

It works for me without a problem. What is the error message?