Need help parsing text from a cell please

[rhaas128]

I have thousands of rows in a sheet and one specific column I need to parse text from. If you know a better way to perform this task than what I am about to explain, please feel free to share Basically, I want to look for specific text within the column, and put that text and a bit of text after it into a new cell. See below for an example between the asterisks:

************************************
Request: NeededAccess
Access Number : 01
Access Name : ANAME


Business Justification: New hire for AKFE.
************************************

I want to look for the Access Name. I don't NEED "Access Name", just the name after the colon. However, I can accept this entire line if I must. The catch is, this line may not always exist, or may not be the same line number in each cell within the column. Is this feat possible?

Thanks for any and all help in advance and hopefully this task is clear.

 

[Rick Rothstein]

Thank you both. Both of these options work beautifully!

Just pointing out that you had three solution responses to your question... I think you may have overlooked the formula solution that konttaruan gave you in Message #4 which also works.

 

[rhaas128]

You are correct sir! I completely overlooked it. Thanks to all 3 of you

 

[rhaas128]

Here is another macro that you can consider...

[TABLE]
<tbody>[TR]
[TD]Sub GetAccessName()
  Dim R As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    If InStr(Data(R, 1), "Access Name : ") Then
      Data(R, 1) = Split(Split(Data(R, 1), "Access Name : ", , vbTextCompare)(1), vbLf)(0)
    Else
      Data(R, 1) = "No Match"
    End If
  Next
  Range("B2").Resize(UBound(Data)) = Data
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Question on this one, let's say the Access Name is actually (with the following quotes and period) "Pharmacy Tech". Right now I have managed to ignore the first " by splitting after it. However, the last ". will still be included as it is part of the line. Is there a way to ignore that portion of the line?

 

[Rick Rothstein]

Question on this one, let's say the Access Name is actually (with the following quotes and period) "Pharmacy Tech". Right now I have managed to ignore the first " by splitting after it. However, the last ". will still be included as it is part of the line. Is there a way to ignore that portion of the line?

See if this modification to my code does what you want...

Sub GetAccessName()
  Dim R As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    If InStr(Data(R, 1), "Access Name : ") Then
      Data(R, 1) = Replace(Replace(Split(Split(Data(R, 1), "Access Name : ", , _
                   vbTextCompare)(1), vbLf)(0), """.", ""), """", "")
    Else
      Data(R, 1) = "No Match"
    End If
  Next
  Range("B2").Resize(UBound(Data)) = Data
End Sub

 

[rhaas128]

See if this modification to my code does what you want...

Sub GetAccessName()
  Dim R As Long, Data As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    If InStr(Data(R, 1), "Access Name : ") Then
      Data(R, 1) = Replace(Replace(Split(Split(Data(R, 1), "Access Name : ", , _
                   vbTextCompare)(1), vbLf)(0), """.", ""), """", "")
    Else
      Data(R, 1) = "No Match"
    End If
  Next
  Range("B2").Resize(UBound(Data)) = Data
End Sub

This works perfect. Thanks again!