InStr for middle name

[Fiske]

Hi i am doing a code on InStr and got stuck at this.
The Data in column B is the name while the code will input the middle name in Column C
Example: John_p.Doe result will p
i got a code but it keep saying invalid procedure and i dont know why please help thanks!

Cells(i, "C").Value = Trim(Mid(partno, InStr(1, partno, " ") + 1, InStr(InStr _
(1, partno, "_") - 1, partno, " ") - InStr(1, partno, ".")))

 

[Rick Rothstein]

Here is a way to do it without using any loops...

And here is another method that uses a loop...

[SIZE=2]Sub MiddleInitial()
  Dim X As Long
  Worksheets("Sheet1").Activate
  For X = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(X, "C") = Split(Replace(Cells(X, "B"), "_", "."), ".")(1)
  Next
End Sub[/SIZE]

 

[Fiske]

Thanks for all the codes! will look into it and study it ^^