On 23 Dez., 15:57, "Peter T" <peter_t@discussions wrote:
Range("A1").NumberFormat = "(##-###-##-##)"
Range("A1") = 123456789
Columns("A:A").EntireColumn.AutoFit

Manually, select your cells, Ctrl-1, Custom, Type: the above number-format
without the quotes

FWIW, numbers in brackets are typically regarded as -ve. Post back if that's
the objective as the -ve format should follow the +ve format, separated with
a comma.

Regards,
Peter T

"andreashermle" wrote in message

...



Dear Experts:

I would like to format selected figures with a specific syntax so that
after running the macro parentheses are put around the numbers

Befo
xx-xxx-xx-xx (x stands for any number 0-9)

After (the result):
(xx-xxx-xx-xx)

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -


Dear Peter T:

thank you very much for your professional help. It works as desired.
But I am afraid to tell you that I was looking for a VBA solution that
inserts the brackets in selected cells automatically.

Please find below a VBA solution which I came across in the meantime.
Thank you again for your valuable help. Regards, Andreas

Sub PutBracketsAroundFigures()
Dim ws As Worksheet
Dim cel As Range
Dim str As String
Const expr As String = "[(]?[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9]-[0-9]
[0-9][)]?"


For Each cel In Selection
str = fnRegEx(cel.Text, expr)
If str < "" Then
If Left(str, 1) < "(" And Right(str, 1) < ")" Then
cel = Replace(cel, str, "(" & str & ")")

End If
Next


End Sub

Function fnRegEx(str As String, expr As String) As String
Dim regex As Object

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = expr
If regex.test(str) Then
fnRegEx = regex.Execute(str)(0)
Else
fnRegEx = ""
End If

Set regex = Nothing
End Function