Parse Name from one sheet to another

jonllaa · #1 (**permalink**) 08-21-09, 15:12

Ok, now that I have a flat forehead from repeatedly banging it against the desk, I will ask for help from those who really know what they're doing!
I'm trying to parse First Name out of column B in "PayOriginal" sheet that has "Firstname Lastname" into a column B in the ActiveSheet (Sheet1). Both sheets in the same workbook. The VB compiler keeps saying "Sub or Function not defined" when it hits the SEARCH(...) function. The VBA library is listed as present in the References window.

Sub ParseFirst()

Dim IsLastRow, counter As String

IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.Count

For counter = 2 To IsLastRow

ActiveSheet.Range("B" & counter).Value = Left(Worksheets("PayOriginal").Range("B" & counter).Value, Search(" ", Worksheets("PayOriginal").Range("B" & counter).Value, 1) - 1)

Next
End Sub

The logic is Microsoft's own:
http://office.microsoft.com/en-us/ex...498501033.aspx

What "totally newbie" thing am I doing?
If I can get this to work, parsing the Middle Initial (when there is one) and Last Name should follow. sigh.
Thanks in advance for any advice you all can give.

MikeTheBike · #2 (**permalink**) 08-24-09, 08:32

Quote:

Originally Posted by jonllaa

Ok, now that I have a flat forehead from repeatedly banging it against the desk, I will ask for help from those who really know what they're doing!
I'm trying to parse First Name out of column B in "PayOriginal" sheet that has "Firstname Lastname" into a column B in the ActiveSheet (Sheet1). Both sheets in the same workbook. The VB compiler keeps saying "Sub or Function not defined" when it hits the SEARCH(...) function. The VBA library is listed as present in the References window.

Sub ParseFirst()

Dim IsLastRow, counter As String

IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.Count

For counter = 2 To IsLastRow

ActiveSheet.Range("B" & counter).Value = Left(Worksheets("PayOriginal").Range("B" & counter).Value, Search(" ", Worksheets("PayOriginal").Range("B" & counter).Value, 1) - 1)

Next
End Sub

The logic is Microsoft's own:
http://office.microsoft.com/en-us/ex...498501033.aspx

What "totally newbie" thing am I doing?
If I can get this to work, parsing the Middle Initial (when there is one) and Last Name should follow. sigh.
Thanks in advance for any advice you all can give.

Hi

Just a little confused as to what precisely you are trying to achieve.

If you are trying to enter a formula in to the cells then I think this will do it

Code:

Sub ParseFirst()

    Dim IsLastRow, counter As Integer
    
    IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.count
    
    For counter = 2 To IsLastRow
        ActiveSheet.Range("B" & counter & "") = "=LEFT(PayOriginal!B" & counter & ",SEARCH("" "",PayOriginal!B" & counter & ",1)-1)"
    Next
End Sub

If tou are trying to enter the actual name then this should do it

Code:

Sub ParseFirst()

    Dim IsLastRow, counter As Integer
    
    IsLastRow = Worksheets("PayOriginal").UsedRange.Rows.count
    
    For counter = 2 To IsLastRow
    
    ActiveSheet.Range("B" & counter) = Left(Worksheets("PayOriginal").Range("B" & counter), InStr(Worksheets("PayOriginal").Range("B" & counter), " ") - 1)
    
    Next
End Sub

The Microsoft site you referred to was an illustration of worksheet formulas not VBA.

You will also notice I changed the valuables to integers.

However, you will (may) need to address the issue of no space in the search text with both these solutions (if there is a possibility of this occurring?).

HTH

MTB

jonllaa · #3 (**permalink**) 08-25-09, 11:22

Hi Mike, Thanks alot for pointing me in the right direction. Sorry for not being clear -- I did want to parse the actual name parts into separate columns. I haven't had to program in VBA for quite a few years and it's changed a bit (no surprise). The real nut to crack was the fact that my input data is not consistently formatted. Some have no middle initial (and so only 1 space in the name), some have one letter MidInit, some have letter + period, some have the MidIniti appended the the first or last name with an underscore, etc. Getting the first name was no problem, but getting the MidInit correctly and then the last name correctly was more challenging. Thanks to your help, here is the ugly final solution. You have the run the MidInit subroutine before you run the LastName sub in order to get the last name out correctly.

Code:

Dim IsLastRow, counter, MiCount As Integer
Dim MiChar, MiLastChar, LastCellAddress As String

Sub ParseFirst()

IsLastRow = ActiveSheet.UsedRange.Rows.Count

For counter = 2 To IsLastRow

' Parse out first name from Column B PayOriginal sheet into Sheet1.Column B
ActiveSheet.Range("D" & counter).Value = Left(ActiveSheet.Range("B" & counter).Value, InStr(ActiveSheet.Range("B" & counter), " ") - 1)

Next
End Sub

Sub ParseMiddle()

IsLastRow = ActiveSheet.UsedRange.Rows.Count

For counter = 2 To IsLastRow

MiChar = ""
MiCount = 0

' Parse out Middle Initial, if there is one, into Sheet1.Column C
MiChar = Mid(ActiveSheet.Range("B" & counter).Value, InStr(ActiveSheet.Range("B" & counter).Value, " ") + 1, InStr(InStr(ActiveSheet.Range("B" & counter).Value, " ") + 1, ActiveSheet.Range("B" & counter).Value, " ") - InStr(ActiveSheet.Range("B" & counter).Value, " ") - 1)

On Error Resume Next
MiCount = Len(MiChar)

'Check if the last character in MiChar is a period, if it is, put the whole thing in sheet1.Column C as MI
MiLastChar = Right(MiChar, 1)
If MiCount > 1 And Asc(MiLastChar) = 46 Then
   ActiveSheet.Range("E" & counter).Value = MiChar
ElseIf MiCount = 1 Then
  ActiveSheet.Range("E" & counter).Value = MiChar
Else
  ActiveSheet.Range("E" & counter).Value = ""
End If

MiChar = ActiveSheet.Range("E" & counter).Value
On Error Resume Next
ActiveSheet.Range("E" & counter).Value = RTrim(MiChar)

Next
End Sub

Sub ParseLast()

IsLastRow = ActiveSheet.UsedRange.Rows.Count

For counter = 2 To IsLastRow

'If text after 1st space is not MI, then extract Last Name begining from 1st space and put into Sheet1.ColumnA, else extract last name beginning from 2nd space into Sheet1.ColumnA
If ActiveSheet.Range("E" & counter).Value = "" Then
ActiveSheet.Range("C" & counter).Value = Right(ActiveSheet.Range("B" & counter).Value, Len(ActiveSheet.Range("B" & counter).Value) - InStr(1, ActiveSheet.Range("B" & counter).Value, " ", vbCompareText) + 1)
Else
ActiveSheet.Range("C" & counter).Value = Right(ActiveSheet.Range("B" & counter).Value, Len(ActiveSheet.Range("B" & counter).Value) - InStr(InStr(1, ActiveSheet.Range("B" & counter).Value, " ", vbTextCompare) + 1, ActiveSheet.Range("B" & counter).Value, " ", vbTextCompare))
End If

MiChar = ActiveSheet.Range("C" & counter).Value
ActiveSheet.Range("C" & counter).Value = Trim(MiChar)

Next
End Sub

I was getting a 5 error when it hit a name with only one space in it, thus the ugly error handling.

Sigh. It ain't pretty, but it works. Now I have to do a two-sheet compare with lots of convoluted selection criteria. double sigh.

Thanks again Mike! -- jon

Internet Services

Web Development

Digital Marketing

Consumer Tech