Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Columns (Bracketing Text)
I have a need to combine two columns, which I can do using the features from
PUP 6. What I would like to know how to do before I combine the columns, if any of the cells in one column, (bracket) that information before combining -- Regards Michael Koerner |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Columns (Bracketing Text)
Think you left something out of your statement
If any of the cells in one column ? ? ?, Also, What do you mean by bracket? Do you want to indicate missing values by putting brackets in the string "AAA" & "()" If you physically want to put in the brackets Dim rng as Range, rng1 as Range set rng = Intersect(Range("A:B"),Activesheet.UsedRange) On Error Resume Next set rng1 = rng.Specialcells(xlBlanks) On error goto 0 if not rng1 is nothing then rng1.Value = "()" End if -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... I have a need to combine two columns, which I can do using the features from PUP 6. What I would like to know how to do before I combine the columns, if any of the cells in one column, (bracket) that information before combining -- Regards Michael Koerner |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Columns (Bracketing Text)
Sub AddBrackets()
Dim rng as Range, cell as Range set rng = Columns(2).SpecialCells(xlConstants) for each cell in rng if len(trim(cell)) 0 then cell.Value = "(" & cell.Value & ")" end if Next End Sub -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... Sorry about that I should have read more closely what I wrote, and should not compose until I have had my first coffee <g In col B, if there is data in any of the cells, then put it into (brackets) -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Think you left something out of your statement If any of the cells in one column ? ? ?, Also, What do you mean by bracket? Do you want to indicate missing values by putting brackets in the string "AAA" & "()" If you physically want to put in the brackets Dim rng as Range, rng1 as Range set rng = Intersect(Range("A:B"),Activesheet.UsedRange) On Error Resume Next set rng1 = rng.Specialcells(xlBlanks) On error goto 0 if not rng1 is nothing then rng1.Value = "()" End if -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... I have a need to combine two columns, which I can do using the features from PUP 6. What I would like to know how to do before I combine the columns, if any of the cells in one column, (bracket) that information before combining -- Regards Michael Koerner |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Columns (Bracketing Text)
Tom;
That worked great, thank you very much. What would I have to change to make it work on any column I happen to be in? -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Sub AddBrackets() Dim rng as Range, cell as Range set rng = Columns(2).SpecialCells(xlConstants) for each cell in rng if len(trim(cell)) 0 then cell.Value = "(" & cell.Value & ")" end if Next End Sub -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... Sorry about that I should have read more closely what I wrote, and should not compose until I have had my first coffee <g In col B, if there is data in any of the cells, then put it into (brackets) -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Think you left something out of your statement If any of the cells in one column ? ? ?, Also, What do you mean by bracket? Do you want to indicate missing values by putting brackets in the string "AAA" & "()" If you physically want to put in the brackets Dim rng as Range, rng1 as Range set rng = Intersect(Range("A:B"),Activesheet.UsedRange) On Error Resume Next set rng1 = rng.Specialcells(xlBlanks) On error goto 0 if not rng1 is nothing then rng1.Value = "()" End if -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... I have a need to combine two columns, which I can do using the features from PUP 6. What I would like to know how to do before I combine the columns, if any of the cells in one column, (bracket) that information before combining -- Regards Michael Koerner |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Columns (Bracketing Text)
Sub AddBrackets()
Dim rng as Range, cell as Range On error resume Next set rng = ActiveCell.EntireColumn.SpecialCells(xlConstants) On error goto 0 if not rng is nothing then for each cell in rng if len(trim(cell)) 0 then cell.Value = "(" & cell.Value & ")" end if Next end if End Sub -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... Tom; That worked great, thank you very much. What would I have to change to make it work on any column I happen to be in? -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Sub AddBrackets() Dim rng as Range, cell as Range set rng = Columns(2).SpecialCells(xlConstants) for each cell in rng if len(trim(cell)) 0 then cell.Value = "(" & cell.Value & ")" end if Next End Sub -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... Sorry about that I should have read more closely what I wrote, and should not compose until I have had my first coffee <g In col B, if there is data in any of the cells, then put it into (brackets) -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Think you left something out of your statement If any of the cells in one column ? ? ?, Also, What do you mean by bracket? Do you want to indicate missing values by putting brackets in the string "AAA" & "()" If you physically want to put in the brackets Dim rng as Range, rng1 as Range set rng = Intersect(Range("A:B"),Activesheet.UsedRange) On Error Resume Next set rng1 = rng.Specialcells(xlBlanks) On error goto 0 if not rng1 is nothing then rng1.Value = "()" End if -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... I have a need to combine two columns, which I can do using the features from PUP 6. What I would like to know how to do before I combine the columns, if any of the cells in one column, (bracket) that information before combining -- Regards Michael Koerner |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Columns (Bracketing Text)
Tom;
Thank you very much, greatly appreciated. -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Sub AddBrackets() Dim rng as Range, cell as Range On error resume Next set rng = ActiveCell.EntireColumn.SpecialCells(xlConstants) On error goto 0 if not rng is nothing then for each cell in rng if len(trim(cell)) 0 then cell.Value = "(" & cell.Value & ")" end if Next end if End Sub -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... Tom; That worked great, thank you very much. What would I have to change to make it work on any column I happen to be in? -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Sub AddBrackets() Dim rng as Range, cell as Range set rng = Columns(2).SpecialCells(xlConstants) for each cell in rng if len(trim(cell)) 0 then cell.Value = "(" & cell.Value & ")" end if Next End Sub -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... Sorry about that I should have read more closely what I wrote, and should not compose until I have had my first coffee <g In col B, if there is data in any of the cells, then put it into (brackets) -- Regards Michael Koerner "Tom Ogilvy" wrote in message ... Think you left something out of your statement If any of the cells in one column ? ? ?, Also, What do you mean by bracket? Do you want to indicate missing values by putting brackets in the string "AAA" & "()" If you physically want to put in the brackets Dim rng as Range, rng1 as Range set rng = Intersect(Range("A:B"),Activesheet.UsedRange) On Error Resume Next set rng1 = rng.Specialcells(xlBlanks) On error goto 0 if not rng1 is nothing then rng1.Value = "()" End if -- Regards, Tom Ogilvy "Michael Koerner" wrote in message ... I have a need to combine two columns, which I can do using the features from PUP 6. What I would like to know how to do before I combine the columns, if any of the cells in one column, (bracket) that information before combining -- Regards Michael Koerner |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I automate bracketing of text in an Excel cell? | Excel Discussion (Misc queries) | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
Double quotes bracketing text strings that include commas | Excel Discussion (Misc queries) | |||
combining two text columns into one | Excel Discussion (Misc queries) | |||
How Do I use COUNTIF Combining the Text from 2 Columns | Excel Worksheet Functions |