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],"" "",""""))))))

 

[brooksc29]

well, if it's working for you, how can I work it into the macro I already have built, or what piece of that macro needs to be replaced? Putting it in it's own module won't work for me as this needs to be applied to over 200 worksheets in the workbook

 

[brooksc29]

also, keep in mind this data is imported from IE from a website and the URLs for where the data comes from are manipulated and altered from a "setup" page in the workbook. The user chooses a few variables that change the URLs and the new data is imported... that is why I need to use VBA code.

 

[jreedich]

Change the line of code to

myModifiedName = Left(myName, 1) & ". " & Right(myName, Len(myName) - InStr(myName, " "))

 

[jreedich]

Try This and see how it works

code:
Sub gpNames()
Dim myName As String
Dim myModifiedName As String
Dim pvRow As Integer
Dim pvCol As Integer

pvCol = ActiveCell.Column
pvRow = ActiveCell.Row
Do Until Cells(pvRow, pvCol) = ""
myName = Cells(pvRow, pvCol)
myModifiedName = Left(myName, 1) & ". " & Right(myName, Len(myName) - InStr(myName, " "))
Cells(pvRow, pvCol + 1) = myModifiedName
pvRow = pvRow + 1
Loop
End Sub

 

[brooksc29]

do you know of any way to just add to the formula I already have

=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],"" "",""""))))))"

so that this pulls the last name and first initial?

I think there is too much in the VBA code that I already have set up to make your piece work without having to change a lot. It would be easiest if I can just change what I already have

 

[brooksc29]

Try This and see how it works

code:
Sub gpNames()
Dim myName As String
Dim myModifiedName As String
Dim pvRow As Integer
Dim pvCol As Integer

pvCol = ActiveCell.Column
pvRow = ActiveCell.Row
Do Until Cells(pvRow, pvCol) = ""
myName = Cells(pvRow, pvCol)
myModifiedName = Left(myName, 1) & ". " & Right(myName, Len(myName) - InStr(myName, " "))
Cells(pvRow, pvCol + 1) = myModifiedName
pvRow = pvRow + 1
Loop
End Sub

sub this out for my current macro?

 

[jreedich]

can you paste your whole macro here so we can see why you just do not replace it with one of the solutions that have benn posted?

 

[brooksc29]

first i run this 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


then this one
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'
' This part moves to Cell A1 then deletes column A

Range("A1").Select
Selection.EntireColumn.Delete

 

[brooksc29]

the first macro does this

This macro will insert a new column B, go to B2 of the inserted column and enter a formula that extracts the last name in a string in a cell and it then inserts the formula in all blank cells beside cells that contain first and last names.

 

[jreedich]

So the first portion of the code will create the First Initial and Last Name.
The second portion just gets rid of the formulas
If this is true then the following code will replace your entire macro. You will not need to run two macros.

code:
Sub gpNames()
Dim myName As String
Dim myModifiedName As String
Dim pvRow As Integer
Dim pvCol As Integer

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

pvCol = 1
pvRow = ActiveCell.Row
Do Until Cells(pvRow, pvCol) = ""
myName = Cells(pvRow, pvCol)
myModifiedName = Left(myName, 1) & ". " & Right(myName, Len(myName) - InStr(myName, " "))
Cells(pvRow, pvCol + 1) = myModifiedName
pvRow = pvRow + 1
Loop
Range("A1").Select
Selection.EntireColumn.Delete
End Sub

\code: