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

 

[Fiske]

halp!

 

[Michael M]

How about posting ALL the code please??

 

[Fiske]

How about posting ALL the code please??

Sub rev()

Dim partno As String
Dim i As Integer


    Worksheets("sheet1").Activate
        
    For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
        partno = Cells(i, "B").Value
        Cells(i, "C").Value = Trim(Mid(partno, InStr(1, partno, " ") + 1, InStr(InStr _
(1, partno, "_") - 1, partno, " ") - InStr(1, partno, ".")))
    Next
      
End Sub

i am using excel 2003 btw.

 

[ukmikeb]

Hi

This is all you need :-

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

to extract the "p" in your sample.

If other samples fail this statement feel free to come back.

hth

 

[Michael M]

Can't test, but I think this will get you going

Sub rev()
Dim partno As String, i As Integer
    Worksheets("sheet1").Activate
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Cells(i, "C").Formula = "=Mid(" & Cells(i, "B").Address & ", Find(""_""," & Cells(i, "B").Address & ") + 1, Find("".""," & Cells(i, "B").Address & ", 1) - Find("".""," & Cells(i, "B").Address & ", 1) + 1)"
        Cells(i, "C").Value = Cells(i, "C").Value
    Next i
End Sub

 

[Fiske]

Hi Mike! thanks for the reply, your code works great! thanks again.
As i am still a student self-learning VBA, could you explain why my code did not work thanks!

 

[Fiske]

Can't test, but I think this will get you going

Sub rev()
Dim partno As String, i As Integer
    Worksheets("sheet1").Activate
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Cells(i, "C").Formula = "=Mid(" & Cells(i, "B").Address & ", Find(""_""," & Cells(i, "B").Address & ") + 1, Find("".""," & Cells(i, "B").Address & ", 1) - Find("".""," & Cells(i, "B").Address & ", 1) + 1)"
        Cells(i, "C").Value = Cells(i, "C").Value
    Next i
End Sub

Hi Micheal! thanks for the reply! your code works great too but it looks much more complicated. If it is alright, could you explain to me the difference between yours and mine and the one mike gave, as i am still a learning student and would hope to learn more from you guys thanks! ^^

 

[Rick Rothstein]

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

Sub MiddleInitial()
  Dim LastRow As Long
  Worksheets("Sheet1").Activate
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate(Replace("IF(B1:B#="""","""",RIGHT(" & _
                            "LEFT(B1:B#,FIND(""."",B1:B#)-1)))", "#", LastRow))
End Sub

 

[pgc01]

Hi

Another option:

Cells(j, "C").Value = Split(Split(partno, "_")(1), ".")(0)