Extracting multiple words out of a single cell - where do I start?

[christos75]

Hi all, hope someone can help with the following. Using Excel 2016. I'll try and explain what I am trying to do as best I can.

I have a spreadsheet with a lot of data in a single column A. This could be from A1 to A500.
Each cell in the column contains a lot of text from extracted emails including auto signatures. What I want to do is to find the following word (with wildcards), "N???????L" in each cell. It always starts with N and ends with L with 7 numbers inbetween.
It could appear once in the cell and it could also appear 2 times, or 3 times or even 15 times. I want to extract all the words "N???????L" from the cell and copy to another column, each in its own row. And then do this for all cells in the column.

Is this possible?
I have not been able to find a way to do this and do not even know where to begin so I don't have a sample code of what I've tried.

Thank you.

 

[Fennek]

Hello,

try with

with cells(1,1)
for i = 1 to len(.value) -9
     if mid(.value,i,9) like "N#######L" then msgbox "found"
next i
end with

 

[Johnny C]

You need VBA and the Like operator. InStr doesn't take wildcards (unless it changed in XL2016)

For Cellcount = 1 to 500
    MyCell = cells(CellCount,1).Value
    FoundCount = 0
    For CharCount = 1 to Len(MyCell)
        if Mid(Mycell,CharCount,9) Like "[N]???????[L]" Then
            FoundCount = FoundCount + 1
            cells(CellCount,1+FoundCount).Value = Mid(Mycell,CharCount,9)
        End if
    next CharCount
Next CellCount

 

[István Hirsch]

You need VBA and the Like operator. InStr doesn't take wildcards (unless it changed in XL2016)

For Cellcount = 1 to 500
    MyCell = cells(CellCount,1).Value
    FoundCount = 0
    For CharCount = 1 to Len(MyCell)
        if Mid(Mycell,CharCount,9) Like "[N]???????[L]" Then
            FoundCount = FoundCount + 1
            cells(CellCount,1+FoundCount).Value = Mid(Mycell,CharCount,9)
        End if
    next CharCount
Next CellCount

It extracts NNNNNNNNL.

 

[mikerickson]

Try
"N#######L" (as in Fennek's code)
or
"N[0-9][0-9][0-9][0-9][0-9][0-9][0-9]L"

 

[christos75]

You guys are awesome. It worked. THANK YOU!!!!!

For CellCount = 1 To 500
Mycell = Cells(CellCount, 1).Value
FoundCount = 0
For CharCount = 1 To Len(Mycell)
If Mid(Mycell, CharCount, 9) Like "N#######L" Then
FoundCount = FoundCount + 1
Cells(CellCount, 1 + FoundCount).Value = Mid(Mycell, CharCount, 9)
End If
Next CharCount
Next CellCount

 

[István Hirsch]

You guys are awesome. It worked. THANK YOU!!!!!

For CellCount = 1 To 500
Mycell = Cells(CellCount, 1).Value
FoundCount = 0
For CharCount = 1 To Len(Mycell)
If Mid(Mycell, CharCount, 9) Like "N#######L" Then
FoundCount = FoundCount + 1
Cells(CellCount, 1 + FoundCount).Value = Mid(Mycell, CharCount, 9)
End If
Next CharCount
Next CellCount

I think this is of very small importance from practical point of view, just to mention: your code extracts not only "words", as you have mentioned in post #1 , but extracts N1234567L also from NN1234567LIST.

 

[mikerickson]

This will not find NN1234567LIST

Dim oneWord as Variant

For CellCount = 1 To 500
    Mycell = Cells(CellCount, 1).Value
    FoundCount = 0
    For Each oneWord in Split(MyCell, " ")
        If oneWord Like "N#######L" Then
            FoundCount = FoundCount + 1
            Cells(CellCount, 1 + FoundCount).Value = Mid(Mycell, CharCount, 9)
        End If
    Next oneWord
Next CellCount