pass text only from a textbox to a cell

[rickblunt]

Greetings, if I have a combination of text and numbers in a userform textbox, how would I tweak my code to ONLY send the text to a cell in a worksheet?

For example in my code snippet below the value in "deviceId" is "fire extinguisher 47". but I would like only the text "fire extinguisher" to be passed to the next blank cell in column B.

.Range("B" & nextrow) = DeviceId.Value

I know how to do this in a formula, but I have never done it in code. I appreciate any assistance - thank you

 

[Gokhan Aycan]

You can use worksheet functions as Application.WorksheetFunction.<function>.....

 

[rickblunt]

Yes, I know that this is one way but I should be able to do it simply by applying the desired format (parse it out) to my value as i pass it to the workbook. I did this on a app I made up years ago, because I needed to pass numbers over. It was just a single line added to the string. But I can't remember the exact syntax and I don't even remember where I used it exactly so i could look it up :/....

 

[Gokhan Aycan]

This function should achieve what you want:

Public Function Strip_Numbers(ByVal str As String) As String

    Dim oRegExp As Object
   
    Set oRegExp = CreateObject("vbscript.RegExp")
   
    oRegExp.Pattern = "\d"
    oRegExp.Global = True
   
    str = Trim(oRegExp.Replace(str, ""))
       
    Set oRegExp = Nothing

End Function

Usage Example:

Debug.Print Strip_Numbers("fire extinguisher 47")

 

[rickblunt]

Thanks Gokhan, I will try it out. I haven't used functions (though I understand why they are a good thing to use) that often so this will give me the impetus to start looking at these closer.