CODEWITHSUNDEEP
excelexcel-formulavba
Ja Nosch
Ja Nosch

Reputation: 423

extract textpattern from excel cell

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?

Upvotes: 1

Views: 125

Answers (2)

user4039065
user4039065

Reputation:

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 or .

¹ 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).

Upvotes: 0

eshwar
eshwar

Reputation: 694

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.

Upvotes: 1

Related Questions