extract textpattern from excel cell

Question

I have an excel table with around 500 rows. one column (D) contains a text and somewhere in that text there might be a ISBN number, looking something like this "ISBN 123-456-67-8-90". I would like to extract that ISBN (remove it from the cell) and move it to a different cell in the same row (K).

So far I have been able to build a regex for my string

[ISBN]+ [0-9]+-[0-9]+-[0-9]+-[0-9]+-[0-9]+

And I think I also have a rough concept of the outer mechanism that matches my cells:

Sub MoveISBN() 
Dim myrange, cell As Range 
Set myrange = ActiveSheet.Range("D:D", Range("D:D").End(xlDown)) 
For Each cell In myrange 
If *** HERE GOES MY REGEX SOMEHOW ***
Then *** HERE THE FOUND ISBN IS REMOVED FROM THE CURRENT CELL AND MOVED TO COL K ***
End If 
Next cell

Can someone point me in the right direction?

Is it **guaranteed** to be the format `ISBN ###-###-##-#-##`? — BruceWayne, Sep 23 '16 at 21:10
This is really almost a duplicate of [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) — Ron Rosenfeld, Sep 24 '16 at 00:31

score 1 · Accepted Answer · answered Sep 24 '16 at 04:37

I have a ready formula for this in case you want to extract just the ISBN number.

=LEFT(RIGHT(SUBSTITUTE(A2,"ISBN ","|"),LEN(SUBSTITUTE(A2,"ISBN ","|"))-FIND("|",SUBSTITUTE(A2,"ISBN ","|"))),IFERROR(FIND(" ",RIGHT(SUBSTITUTE(A2,"ISBN ","|"),LEN(SUBSTITUTE(A2,"ISBN ","|"))-FIND("|",SUBSTITUTE(A2,"ISBN ","|")))),LEN(RIGHT(SUBSTITUTE(A2,"ISBN ","|"),LEN(SUBSTITUTE(A2,"ISBN ","|"))-FIND("|",SUBSTITUTE(A2,"ISBN ","|"))))))

This assumes the source cell is A2 and the ISBN is followed by a "space" or is at the end of the cell.

This will only fetch the ISBN. If you want the ISBN removed from the source cell, you have to do it with VBA only.

I noticed that you have the source in Column D. You can find-replace A2 with D2. — eshwar, Sep 24 '16 at 04:39

score 0 · Answer 2 · edited Sep 21 '17 at 21:54

It is unclear from your code (or lack of sample data) whether your data starts in row 1 or row 2 (with row 1 containing a column header label). In either event, your current code will loop through every cell in column D; a total of 1,048,576 cells and that is probably not preferable.

To define a range of the populated cells in a column, define a Range object by providing the starting cell and then look from the bottom up for the ending cell¹.

Dim myrange
with worksheets("Sheet1")    '<~~ know what worksheet you are on!!!
    'your current method
    Set myrange = ActiveSheet.Range("D:D", Range("D:D").End(xlDown)) 
    debug.print myrange.address(0,0)   '<~~ results in D:D, the entire column

    'with data in D2 to D99 use this alternate
    Set myrange = .Range(.cells(2, "D"), .cells(.rows.count, "D").end(xlup)) 
    debug.print myrange.address(0,0)   '<~~ results in D2:D99, only the populated cells
end with

There are many other examples of when to use a Static variable in excel or excel-vba.

¹ _{See Is the . in .Range necessary when defined by .Cells for more on defining a Range object with a pair of Range.Cells properties.}

² _{A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).}

extract textpattern from excel cell

2 Answers2