1

I need to remove the numeric characters that are separated by white space ONLY in a text string in an Excel cell. For example I have:

johndoe99@mail.com 1 concentr8 on work VARIABLE1 99

I need to get:

johndoe99@mail.com concentr8 on work VARIABLE1

Either formula or VBA script solution is good. Thank you.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Annabanana
  • 91
  • 1
  • 3
  • 13
  • 1
    If you don't mind VBA, you could use a regular expression (`(?<=\s)\d+(?=\s)`. See [here](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835) for how to use regex in Excel VBA – cybernetic.nomad Aug 09 '18 at 15:59
  • I have never worked with regular expressions but I will look into it. How would you extract continuous strings (as defined by being separated by white space) that have alphabetic characters (and possibly some other characters such as numeric characters, ".", "&", etc...)? – Annabanana Aug 09 '18 at 16:27
  • @cybernetic.nomad There's no positive look-behind in RegExp. – JohnyL Aug 09 '18 at 17:35

3 Answers3

1

I think nomad is right that regex is probably a simpler option. However, I also think that by using the Split() and isNumeric() functions I've come up with a good solution here.

Sub test()

    Dim cell As Range
    For Each cell In Range("A1:A10")  'adjust as necessary
        cell.Value2 = RemoveNumbers(cell.Value2)
    Next cell

End Sub

Function RemoveNumbers(ByVal inputString As String) As String

    Dim tempSplit As Variant
    tempSplit = Split(inputString, " ")

    Dim result As String

    Dim i As Long
    For i = LBound(tempSplit) To UBound(tempSplit)
        If Not IsNumeric(tempSplit(i)) Then result = result & " " & tempSplit(i)
    Next i

    RemoveNumbers = Trim$(result)

End Function
Marcucciboy2
  • 3,156
  • 3
  • 20
  • 38
  • I used the function part of your program and it worked pretty well! Thank you. – Annabanana Aug 09 '18 at 20:37
  • This is a part of a macro someone asked me to make to strip commands and syntax off an sql program and return a list of variables a program uses. So I first deleted all the common commands, replaced syntax with spaces, deleted carriage returns and trimmed the list. I ended up with variables (that I want) and numeric parameters (that I needed to sift out). The last step was to remove dupes. I'm sure there is a better way to do it out there but my programs were already in Excel and I did my best. Maybe there is already a good program out there that extracts variables only from sql code? – Annabanana Aug 09 '18 at 20:38
  • @Annabanana mmm I doubt it, that sounds like a pretty unusual request. I imagine that you'd just have to set up a lot of `Replace()` functions to solve it – Marcucciboy2 Aug 09 '18 at 20:47
  • 1
    I used a combination of SUBSTITUTE functions (mainly to get rid of syntax and carriage returns and replace them with spaces) with three user defined functions. One was deleting everything that was on a list of common sql commands, the other (used your function, thank you) was deleting numbers and the third was removing duplicates (already had it). In the end I of course had to trim the resulting string. Maybe not the best way to do it but it works. – Annabanana Aug 09 '18 at 21:26
  • @Annabanana Eh, I mean it’s more or less what I would’ve done so I wouldn’t worry about it. You got where you were going :) – Marcucciboy2 Aug 09 '18 at 22:24
0

UDF

Function RemNum(cell)
    With CreateObject("VBScript.RegExp")
        .Global = True: .Pattern = "\s\d+"
        RemNum = .Replace(cell, vbNullString)
    End With
End Function
JohnyL
  • 6,894
  • 3
  • 22
  • 41
  • You are correct, there are no look-behinds (I can't believe I never needed one in Excel before). I would however change your pattern to `\s\d+\s` to match the OP's requirement exactly – cybernetic.nomad Aug 09 '18 at 17:44
0

Note that in addition to testing for spaces before and after, this also tests for the beginning or end of the string as a delimiter.

You did not indicate the case where the number is the only contents of the string. This routine will remove it but, if you want something else, specify.

Try this:

Function remSepNums(S As String) As String
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(?:\s+|^)(?:\d+)(?=\s+|$)"
    .MultiLine = True
    remSepNums = .Replace(S, "")
End With
End Function

Just for fun, if you have a recent version of Excel (Office 365/2016) you can use the following array formula:

=TEXTJOIN(" ",TRUE,IF(NOT(ISNUMBER(FILTERXML("<t><s>"&SUBSTITUTE(TRIM(A1)," ","</s><s>")&"</s></t>","//s"))),FILTERXML("<t><s>"&SUBSTITUTE(TRIM(A1)," ","</s><s>")&"</s></t>","//s"),""))
  • FILTERXML can be used to split the string into an array of words, separated by spaces
  • If any word is not a number, return that word, else return a null string
  • Then join the segments using the TEXTJOIN function.
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60