Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 123
Default Put parentheses around figures

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default Put parentheses around figures

something like this
Range("A1").Value = "(" & Range("A1").Value & ")"

"andreashermle" wrote:

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
.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default Put parentheses around figures

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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 123
Default Put parentheses around figures

On 23 Dez., 15:18, Mike wrote:
something like this
Range("A1").Value = "(" & Range("A1").Value & ")"



"andreashermle" wrote:
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 Mike,

Great job, Mike

ok, it works as desired. But I am afraid to tell you that I would like
the macro to work only on selected cells without my having to adjust
the VBA code each time.

Hope this is not asking too much.

Regards, Andreas
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Put parentheses around figures

If the parentheses only need to be displayed and not actually become part of
the text, you could use this Custom Format on the cells...

(@)

The parentheses will only show up if there is an entry in the cell.

--
Rick (MVP - Excel)


"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




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 123
Default Put parentheses around figures

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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 123
Default Put parentheses around figures

On 23 Dez., 17:45, "Rick Rothstein"
wrote:
If the parentheses only need to be displayed and not actually become part of
the text, you could use this Custom Format on the cells...

(@)

The parentheses will only show up if there is an entry in the cell.

--
Rick (MVP - Excel)

"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 Rick

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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 123
Default Put parentheses around figures

On 28 Dez., 16:59, "Rick Rothstein"
wrote:
Than seems like a lot more code than is necessary for what you asked. If
your question is as you originally stated it, namely, that you just want to
put parentheses around the values in the selected cells (no matter what
those values are), then try this macro...

Sub PutBracketsAroundFigures()
* Dim Cell As Range
* For Each Cell In Selection
* * Cell.NumberFormat = "@"
* * Cell.Value = "(" & Cell.Value & ")"
* Next
End Sub

If, on the other hand, you only want to put parentheses around those values
having the shape of the example number you posted (this is what your
solution code is doing), then try this macro instead...

Sub PutBracketsAroundFigures()
* Dim Cell As Range
* For Each Cell In Selection
* * If Cell.Value Like "??-???-??-??" Then
* * * Cell.NumberFormat = "@"
* * * Cell.Value = "(" & Cell.Value & ")"
* * End If
* Next
End Sub

--
Rick (MVP - Excel)

"andreashermle" wrote in message

...



On 23 Dez., 17:45, "Rick Rothstein"
wrote:
If the parentheses only need to be displayed and not actually become part
of
the text, you could use this Custom Format on the cells...


(@)


The parentheses will only show up if there is an entry in the cell.


--
Rick (MVP - Excel)


"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 Rick


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- Zitierten Text ausblenden -


- Zitierten Text anzeigen -



Hi Rick,

Great help. It works just fine. It is much easier to understand. Have
a nice new year's eve and all the best for 2010.

I appreciate your terrific support.

Regards, Andreas
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
parentheses around a capital C gailrolfe Excel Discussion (Misc queries) 3 September 11th 07 03:28 PM
number in parentheses Mike Excel Discussion (Misc queries) 6 May 10th 07 10:47 PM
add figures to existing figures in excel Barkster Excel Worksheet Functions 0 June 21st 06 02:54 PM
parentheses Brendy Excel Worksheet Functions 5 January 15th 06 10:24 PM
Changing positive figures to minus figures Louise Excel Worksheet Functions 2 September 14th 05 10:05 AM


All times are GMT +1. The time now is 05:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"