Extract String After Specific Word

**Gingeiko** · 12-02-2011, 01:58 AM

Hi,

I have a report of 20000+ rows. I need to extract certain data from one of the column. For the example below, I need to extract the 9 digits that comes after 'SN#'.

May I know if this is possible, given the complexity of the data..

[6 rows under same column]:
[R1]Record - DN No. 98765421 SO# 1343434 for CUSROMER 1 - SN# - 07/29/2011'
[R2]Record - DN No. 12345677 SO# 1222222 for CUSTOMER 2 - SN# - 07/29/
[R3]Hardness Test Results SN# - SN# 133449982-1, PO , HEAT# - 07/29/2011'
[R4]Inspection for Main Part SN# - SN# 123456098-1 TO 4, PO , HEAT# - 07/29/2011'
[R5]Dimensional for Main Part SN# - SN# 119221033, PO# HN# - 07/29/2011'
[R6]Dimensional for Sub Part SN# - SN# 120012999-1 TO 5, PO# HN# - 07/29/2011'

I should be getting in another column:
[R1]
[R2]
[R3] 133449982
[R4] 123456098
[R5] 119221033
[R6] 120012999

**nilem** · 12-02-2011, 02:47 AM

UDF

Function SN(s As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "SN# \d{9}"
    SN = Mid(.Execute(s)(0), 5)
End With
End Function

**Gingeiko** · 12-02-2011, 03:10 AM

Thanks Nilem, for your quick reply.

I am still very new to VBA. So far, I've only been using Sub()...End Sub. So, I'm not sure if I am using this right.

Based on the example from my first post, the data is from A1 to A6.

In B1, i placed the formula =SN(A1), and fill to B6. But what I get in B1 to B6 is:
#NAME?
#VALUE!
#NAME?
#NAME?
#NAME?
#NAME?

**Gingeiko** · 12-02-2011, 03:23 AM

Ok I have no idea why, but after switching between windows, when i returned to my worksheet, it is working! Amazing!

THANKS NILEM!

**pike** · 12-02-2011, 04:24 AM

Hi Gingeiko
or if excel function is prefered

=LEFT(B3,4)&IF(ISNUMBER(SEARCH("SN# - SN# ",B3)),MID(B3,SEARCH("SN# - SN# ",B3)+10,9),"")

**Gingeiko** · 12-03-2011, 06:23 AM

Thanks Pike for the reply.

However I think the example I gave has some flaw. Thing is, the number i need to extract doesn't always come with SN# - SN# before it, sometimes it's just SN# followed by 9 digits.

So am I right to say in this case, regular expression is still a better way of doing it? I mean it does exactly what I need, detecting a pattern of SN# \d{9}, and return the 9 digits.

But I do have a question, is it possible to incorporate the user-defined function, as provided by Nilem, into my Sub instead? I have tried to do some research and I believe I should do something as follow:

Extract_9_Digits()
    Dim SN
    Set SN = CreateObject("VBScript.RegExp")
    With SN
        .Pattern = "SN# \d{9}"
    End With
End Sub

I am not sure what comes after that, if I need to search the string in column D, and return the 9 digits in column E.

**DonkeyOte** · 12-03-2011, 07:02 AM

You could certainly persist with VBA & RegExp but I still suspect a formula driven approach would suffice.

Based on your last comment to Pike perhaps:

B1:
=LEFT($A1,4)&IFERROR(MID($A1,LOOKUP(9.99E+307,SEARCH("SN# "&{1,2,3,4,5,6,7,8,9,0},$A1))+LEN("SN# "),9),"")
copied down as appropriate

where A1 holds string

**pike** · 12-03-2011, 07:27 AM

or this ia all i can up with

=LEFT(B3,4)&IF(ISNUMBER(SEARCH(" SN# ?????????-",B3)),MID(B3,SEARCH(" SN# ?????????-",B3)+4,9),IF(ISNUMBER(SEARCH(" SN# ?????????,",B3)),MID(B3,SEARCH(" SN# ?????????,",B3)+4,9),""))

**nilem** · 12-03-2011, 07:50 AM

or

Sub Extract_9_Digits()
'Dim tm!: tm = Timer
Dim r As Range
With CreateObject("vbscript.regexp")
    .Pattern = "SN# \d{9}"
    For Each r In Range("D1:D" & Cells(Rows.Count, 4).End(xlUp).Row)    'search the string in column D,
        If .test(r) Then r.Next = Mid(.Execute(r)(0), 5)  '...and return the 9 digits in column E
    Next
End With
'MsgBox Timer - tm '21000 rows - 1.01 sec
End Sub

**Gingeiko** · 12-03-2011, 09:05 AM

WOW...thanks Donkeyote, Pike and Nilem for your quick replies.

I'm pretty new to macros, so all this looks really impressive to me, both Formulas and RegExp. and... I would need some time to digest them! Thanks a lot all of you!

Extract String After Specific Word

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Re: Extract String After Specific Word

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions