Parsing text from a file list

[fleathedog]

Hi everyone,

I've got what it probably a fairly simple question for the gurus on here, but I just can't get it right.

I've got a list of files that follows 1 of 2 patterns:
1234567890 Name1.pdf
2345678901 Name2blah blah.pdf

And I want just the first word after the 10 digit number. So my desired output from the above would be:
Name1
Name2blah

My approach has been to use the MID function, starting from character 12, but can't work out the combination of functions to find the number of characters to cut off the end.

Thanks!

 

[LazyBug]

Hello!

Try on the copy of your data: after selection of data in one column macro writes needed word to the next column.

Sub fleathedog()
Dim c As Range, spl
    With ActiveSheet
        For Each c In Selection
            If c.Value Like "* *" Then
                spl = Split(c.Value, " ")
                If IsNumeric(spl(0)) And Len(spl(0)) = 10 Then
                    If spl(1) Like "*.*" Then
                        c.Offset(, 1).Value = Split(spl(1), ".")(0)
                    Else
                        c.Offset(, 1).Value = spl(1)
                    End If
                End If
            End If
        Next c
    End With
End Sub

 

[John_w]

As a formula:

=MID(LEFT(A2,FIND(".",A2)-1),FIND(" ",A2)+1,LEN(A2))

Or a macro with the cells selected puts each result in the cell to the right.

Sub Get_Words()

    Dim cell As Range, p1 As Long, p2 As Long
    
    For Each cell In Selection
        p1 = InStr(cell.Value, " ")
        p2 = InStr(p1, cell.Value, ".")
        cell.Offset(, 1).Value = Mid(cell.Value, p1 + 1, p2 - p1 - 1)
    Next
    
End Sub

 

[CephasOz]

or even

Sub Test()
    Dim rngCell As Range
    For Each rngCell In Selection
        With rngCell
            .Offset(, 1).Value = Replace(Split(.Text & Space(1), Space(1))(1), ".pdf", vbNullString)
        End With
    Next
End Sub

 

[fleathedog]

Thanks for your thoughts guys! As often happens, as soon as I explained the problem to others, a light bulb went off and ended up solving it myself, as below:

=MID(B4,12,IFERROR(FIND(" ",B4,12)-12,FIND(".",B4)-12))