VBA Macro for Phone Number Formatting

[Am_100000]

Hello, I'm relatively new to macros and I have been trying to write a macro that will enable me to format phone numbers into (XXX) XXX-XXXX regardless of the manner or format the original phone number is. What stumps me is when there is an area code in the original phone number i.e. +1 440 299 8000, instead of returning this as (440) 299-8000, it shows (144) 029-98000. I've tried everything I can (including asking for help from ai but still no luck). I hope you can help me with this one. Thank you!

 

[Micron]

There might be an easier way than what I can suggest, which might be to use Regular Expressions, but I have never been able to wrap my head around that. So I will say that to use straight vba you need to explain what the possibilities are wrt number data so that rules can be coded. F'rinstance, if some strings won't start with 1, or some spaces may be missing (1 4402998000) etc. Would be good if defining these possibilities that you show what the string looks like when the area code is not there.

You also need to provide your code so that no one has to start from scratch and ask a bunch of questions that the code could reveal the answers to. When you post code, do so within code tags (use vba button on forum posting toolbar) to maintain indentation and readability.

EDIT - a simple and direct answer based on your image would be to use
Replace(strIn, " ", "-") where strIn is the variable holding the number. Result: +1-414-289-8000

 

[JEC]

You might try this UDF (regular expression)

Function jec(c As String) As String
  With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\s"
    jec = .Replace(c, "")
    .Pattern = "(?:\+[1-9])?(\d{3})(\d{3})(\d{4})"
    jec = .Replace(jec, "($1) $2-$3")
  End With
End Function

Book1
	A	B
1	440 299 8000	(440) 299-8000
2	+1 4402998000	(440) 299-8000
3	+1440 299 8000	(440) 299-8000
4	+1 440 299 8000	(440) 299-8000
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=jec(A1)

 

[Am_100000]

You might try this UDF (regular expression)

Function jec(c As String) As String
  With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "\s"
    jec = .Replace(c, "")
    .Pattern = "(?:\+[1-9])?(\d{3})(\d{3})(\d{4})"
    jec = .Replace(jec, "($1) $2-$3")
  End With
End Function

Book1
	A	B
1	440 299 8000	(440) 299-8000
2	+1 4402998000	(440) 299-8000
3	+1440 299 8000	(440) 299-8000
4	+1 440 299 8000	(440) 299-8000
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=jec(A1)

It worked! Thank you so much