Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parentheses around a capital C | Excel Discussion (Misc queries) | |||
number in parentheses | Excel Discussion (Misc queries) | |||
add figures to existing figures in excel | Excel Worksheet Functions | |||
parentheses | Excel Worksheet Functions | |||
Changing positive figures to minus figures | Excel Worksheet Functions |