How to VBA Excel Macro part of a string

Question

I'm currently busy with Excel tooling and learning a lot but i got a question. Currently i have a couple rows with data in the rows. In the rows there is a lot of data but i need a specific part of the row. Of course i can delete it all manually but to do that for 3000 rows i will be wasting a lot of time.

Can any one help me with a macro that filters data. The data i need is between [ and ] so for example [data]

I hope you guys can help me out and if you need more information just ask me! I hope you guys can help me!

Example String ROW:

[Sandwitch]><xsd:element name="T8436283"

So what do i need?

So i need a macro that only gets the Sandwitch out of it and paste it in the B column. The string with all the information stays at column A and the Sandwitch goes to Column B and that for all rows.

Your question is extremely vague. Please add sample data and expected results. — tigeravatar
– tigeravatar, Commented Sep 12, 2017 at 15:55
Are you saying you have a single cell with info such as words words [important words here] words words and you just want to extract important words here? — BruceWayne
– BruceWayne, Commented Sep 12, 2017 at 15:58
Sorry for the vague question. I added a example. So basically in my string there is a lot of information and i need only the information between the [ and the ]. The other information is not necessary. The String is in column A and only the Sandwitch (In my example) Needs to be placed in column B. — Karel Jansen
– Karel Jansen, Commented Sep 12, 2017 at 16:02
Love the title, reminds me of The Matrix or Hong Kong Fuey. Anyway.... =MID(A1,FIND("[",$A$1)+1,FIND("]",$A$1)-FIND("[",$A$1)-1) — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Sep 12, 2017 at 16:03
@DarrenBartrup-Cook + for Hong Kong Fuey :-) and a solution too. All your noun are belong to verb. — Joffan
– Joffan, Commented Sep 12, 2017 at 16:13

Dipen Desai · Accepted Answer · 2017-09-12 16:14:09Z

2

Option 1: Find/Replace 1) Copy data in another column (just saving original copy) 2) Perform Find/Replace "*[" 3) Perform Find/Replace "]" Now you have data which was between [].

Option 2: Use formulas 1) Lets assume that original data in Column "A" 2) Apply this formula in column "B" which will extract data between [] =MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)

Option 3: Macro If it is absolutely needed, I can help create a macro, otherwise try first two easier options.

answered Sep 12, 2017 at 16:14

Dipen Desai

211 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Karel Jansen Over a year ago

Thank you! Do you also have a formula for between the " and " The 2nd option worked good for me! But i want also in Column C the information that is between " and " if you could help me with that then you made my Day!

Joffan · Accepted Answer · 2017-09-12 17:00:00Z

0

A general purpose "find element in s starting x up to next y":

Function GenExtract(FromStr As String, _
                    StartSep As String, EndSep As String) _
                As Variant

Dim StPos As Long
Dim EnPos As Long

GenExtract = CVErr(xlErrNA)
If StartSep = "" Or EndSep = "" Then Exit Function 'fail
StPos = InStr(1, FromStr, Left(StartSep, 1))
If StPos = 0 Or StPos = Len(FromStr) Then Exit Function 'fail
EnPos = InStr(StPos + 1, FromStr, Left(EndSep, 1))
If EnPos = 0 Then Exit Function 'fail

GenExtract = Mid(FromStr, StPos + 1, EnPos - StPos - 1)

End Function

If the two separators are the same, as per quotes, it gives the first string enclosed by those.

answered Sep 12, 2017 at 17:00

Joffan

1,4801 gold badge14 silver badges19 bronze badges

Comments

interesting-name-here · Accepted Answer · 2017-09-12 19:36:38Z

If you want to get your feet wet in Regular Expressions, the following code will take you there. You have to add a reference to the VB Scripting Library

Tools > References > Microsoft VBScript Regular Expressions 5.5

Then the code is as follows:

Sub textBetweenStuffs()

    Dim str As String
    Dim regEx As RegExp
    Dim m As Match
    Dim sHolder As MatchCollection
    Dim bracketCollection As Collection
    Dim quoteCollection As Collection

    Set regEx = New RegExp

    'Matches anything in between an opening bracket and first closing bracket
    regEx.Pattern = "\[(.*?\])"
    str = "[Sandwitch]><xsd:element name=""T8436283"""

    'populates matches into match collection
    Set sHolder = regEx.Execute(str)

    Set bracketCollection = New Collection

    'loop through values in match collection to do with as you wish
    For Each m In sHolder
        bracketCollection.Add m.Value
    Next i

    Set sHolder = Nothing

    'get values between Quotations
    regEx.Pattern = "\"(.*?\")"

    'populates matches into match collection
    Set sHolder = regEx.Execute(str)

    Set quoteCollection = New Collection

    'loop through values in match collection to do with as you wish
    For Each m In sHolder
        quoteCollection.Add m.Value
    Next i

End Sub

Collectives™ on Stack Overflow

How to VBA Excel Macro part of a string

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related