How do I remove certain text from a cell in Excel?

Question

I have a cell in Excel that contains the following:

@dickfundy @ThatKevinSmith @aliciamalone @MovieMantz @dickfundy nope just Stranger Things Season 2 or Ready Player https:xxxxxxxxxxx

I want to remove the usernames (everything starting with a "@") and I also want to remove the web-link (the "https:xxxxxxxxxxx"), and I want to end up with this in the cell:

nope just Stranger Things Season 2 or Ready Player

I have 60,000 other cells that contain similar text. How do I do this?

It looks like this question is related to another discussion on Stack Overflow available here: https://stackoverflow.com/questions/13957975/delete-data-in-cell-after-specific-character — Harvey A. Ramer, Nov 14 '18 at 01:50
@RonRosenfeld Microsoft Excel for Mac Version 16.16.4 (181110) — rsmalley74, Nov 14 '18 at 02:19
@RonRosenfeld yes but it doesn't apply here as all text is already in the same cell — rsmalley74, Nov 14 '18 at 02:35

score 2 · Answer 1 · answered Nov 14 '18 at 02:29

I believe your version of Excel has the TEXTJOIN function. That being the case you can use this array-formula

=TEXTJOIN(" ",TRUE,IF((LEFT(TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",999)),seq_999,999)))="@")+(LEFT(TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",999)),seq_999,999)),4)="http"),"",TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",999)),seq_999,999))))

where seq_999 is a named formula that

refers to:  =IF(ROW(INDEX(Sheet1!$1:$65535,1,1):INDEX(Sheet1!$1:$65535,255,1))=1,1,(ROW(INDEX(Sheet1!$1:$65535,1,1):INDEX(Sheet1!$1:$65535,255,1))-1)*999)

To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

@rsmalley74 If this is a one-off requirement, the function is OK. If you will be doing this repeatedly on large data sets, a VBA macro will probably run much faster. — Ron Rosenfeld, Nov 14 '18 at 02:45

pnuts · Answer 2 · 2018-11-14T01:26:08.247

1

If you deleted up to the last user name (with Replace *@ with nothing) then you might apply:

=LEFT(MID(A1,FIND(" ",A1),LEN(A1)),FIND("http",MID(A1,FIND(" ",A1)+1,LEN(A1))))

edited Nov 14 '18 at 01:26

answered Nov 14 '18 at 00:34

pnuts

58,317
11
87
139

Thank you. It seems to work however it omits the first word that is leftover. So in the above example the word "nope" is omitted. Also, for other cells where only one word is leftover after applying this formula, the cell is then left blank. – rsmalley74 Nov 14 '18 at 01:15

score 1 · Answer 3 · answered Nov 14 '18 at 01:53

As a UDF:

Function FixIt(s As String) As String
    s = CleanUp(s, "@")
    s = CleanUp(s, "http")
    FixIt = Trim(s)
End Function

'remove all text segments beginning with LookFor, up to the next space
'  or until the end of the input string s
Function CleanUp(s As String, LookFor As String) As String
    Dim pos As Long, pos2 As Long, rv As String
    rv = s
    pos = InStr(1, rv, LookFor)
    Do While pos > 0
        pos2 = InStr(pos + 1, rv, " ")
        If pos2 = 0 Then pos2 = Len(rv)
        rv = Left(rv, pos - 1) & Right(rv, Len(rv) - pos2)
        pos = InStr(1, rv, LookFor)
    Loop
    CleanUp = rv
End Function

If you want something robust:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How do I remove certain text from a cell in Excel?

3 Answers3