Excel VBA - delete string content after word

Question

I'm trying to delete string content before a certain word contained within the string. For example

[email protected]

I'd like to use VBA in order to replace that with

master_of_desaster

Everything after the "word" (@) should be removed, including the "word" itself.

I found a similar topic here, but he asks the opposite.

find the @ using Instr function. Then, just use Left of the original string up to the result of Instr — Shai Rado
– Shai Rado, Commented Sep 20, 2016 at 11:37
If you are doing this on cells, a simple Find/Replace may well be fastest. — Rory
– Rory, Commented Sep 20, 2016 at 12:38

Community · Accepted Answer · 2017-05-23 12:34:57Z

5

email = "[email protected]"

ret = Left(email, InStr(1, email, "@") - 1)

Result: master_of_desaster

Thanks to Shai Rado

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Sep 20, 2016 at 11:42

Black

20.9k47 gold badges188 silver badges300 bronze badges

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

Comments

Nathan_Sav · Accepted Answer · 2016-09-20 11:41:15Z

4

=split("[email protected]","@")(0)

answered Sep 20, 2016 at 11:41

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

1 Comment

Black Over a year ago

If you add comments to it, then i will accept it as the solution. (I understand it, but will others do?)

luke_t · Accepted Answer · 2016-09-20 12:09:47Z

2

Just for fun - a regex approach.

Public Sub reg()

    Dim re_pattern As String
    Dim re As RegExp
    Dim email As String
    Dim match As Object

    Set re = New RegExp

    email = "[email protected]"
    re_pattern = "(.*)@.*"

    With re
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = re_pattern
    End With

    Set match = re.Execute(email)

    Debug.Print match.Item(0).SubMatches(0)

End Sub

answered Sep 20, 2016 at 12:09

luke_t

2,9874 gold badges26 silver badges39 bronze badges

Comments

Slai · Accepted Answer · 2016-09-20 16:12:28Z

2

A bit hacky but fast ( most Windows API accept zero terminated strings )

ret = Replace("[email protected]", "@", vbNullChar, , 1) ' Chr(0)

I usually use the Split method but with Limit:

ret = Split("[email protected]", "@", 2)(0)

edited Sep 20, 2016 at 16:12

answered Sep 20, 2016 at 11:57

Slai

23k5 gold badges49 silver badges55 bronze badges

Comments

Rosetta · Accepted Answer · 2016-09-20 12:14:31Z

1

ret = evaluate("left(" & string & ", search(""@"", " & string & ") - 1)")

answered Sep 20, 2016 at 12:14

Rosetta

2,7351 gold badge17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Excel VBA - delete string content after word

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related