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.

 

[mumps]

I think that we need a little more information. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[rhaas128]

Here is a link to the example doc.

https://app.box.com/s/8bzds44g574bjkz976nltz57jfk7l4fl

 

[konttaruan]

There is probably a shorter way, but try this:

=IFERROR(MID(A3;FIND("Name :";A3)+6;FIND(CHAR(10);A3;FIND("Name :";A3))-FIND("Name :";A3)-6);"No Match")

 

[mumps]

Try:

Sub GetName()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim rngArray As Variant
    Dim i As Long
    For Each rng In Range("A2:A" & LastRow)
        If InStr(1, rng, "Access Name") <> 0 Then
            rngArray = Split(rng, Chr(10))
            For i = LBound(rngArray) To UBound(rngArray)
                If rngArray(i) Like "Access Name :*" Then
                    rng.Offset(0, 1) = Trim(Split(rngArray(i), ":")(1))
                End If
            Next i
        Else
            rng.Offset(0, 1) = "No Match"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="******* 500"]
[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]
[/table]

 

[rhaas128]

Thank you both. Both of these options work beautifully!

 

[mumps]

Glad to help.

 

[konttaruan]

My first attempt to help someone in MrExcelMessageBoard, apparently I failed

 

[mumps]

@ konttaruan: I wouldn't call it a failure. It takes along time to get a good understanding of how Excel formulas and macros work. You learn by doing so that is what you have done. Keep it up. And good luck with it.