Solved Delete unwanted characters in the TextBox Value (1 Viewer)

[Hisoka]

Hello,
I have this code to open a conversation with customers on WhatsApp through the WhatsApp application,

CreateObject("Shell.Application").Open "https://wa.me/" & Me.tbMobNum & "/?text=" & Me.tbWaMessage

As you can see, I have the customer's number in the TextBox, But the number format in the control, like (+968)1111 1111,
But it must be like +96811111111 to work with the code above,
This means that I just want to delete the parentheses and the space in the middle

Maybe VBA Regex will do the trick, But I don't know how to use it, or is there a better way to implement this thing?

Thanks in advance

 

[June7]

Following should serve:
Replace(Replace(Replace(Me.tbMobNum, " ", ""), "(", ""), ")", "")

I expect Regex could do it and if this gets more complicated, might want to or use VBA custom function.

 

[Edgar_]

Here's another:

Function StringToPlusNumber(yourText as String) as string
    Dim res As String
    Dim i As Long
    For i = 1 To Len(yourText)
        If IsNumeric(Mid(yourText, i, 1)) Or Mid(yourText, i, 1) = "+" Then
            res = res & Mid(yourText, i, 1)
        End If
    Next i
    StringToPlusNumber= res
End Function

It's ugly as hell, but it works. OK, I'll add explanation to this ugly thing:
1. Define function, takes string as parameter
2. Loops character by character by checking how many characters it has starting from 1
3. If current character is numeric or a plus sign, it adds it to the result
4. That is all

 

[Hisoka]

Thanks @June7 you're a lifesaver, its work like a charm ..
Thanks @Edgar_ I will try it

 

[Pat Hartman]

Since you are looking at a value from a record rather than a newly entered value, change the name of the control. So, the controlName property is txtPhone and the name of the bound field is Phone. Now, you can refer to the control which is probably where the format is coming from OR you can refer to the actual saved table value which should NEVER have a format associated with it. No Code.

 

[June7]

Doesn't matter if format is generated by Format property of textbox or field in table. It doesn't change saved value. So, my presumption is those unwanted characters are actually in data.

 

[Pat Hartman]

So, my presumption is those unwanted characters are actually in data.

Probably. That means that the best solution is to run an update query to get rid of the characters at the source. Then use input masks on the forms and set them to not save the formatting characters. That means all the suggested code is just bandaids to hide a data problem. Fixing the data problem is always the best solution.

PS, it is really poor practice to use formatting of any kind on your tables because you will never know what the fields actually contain when the formatting masks the actual values.