Auto Insert Parenthesis

How do I auto-insert parethesis around prepopulated data in a worksheet? I 
have a column of information that only if there is information, then I would 
like it to be in parenthesis. For example, 

A1: data
B1: data
C1: no data
D1: no data
E1: data

I would like to auto-insert parenthesis around the cells that show "data" 
but to ignore cells that have "no data". This would involve only 1 column in 
the excel file. The cells that contain data would look like this, example: 
(Smith) or (Jones) rather than Smith or Jones.
1
Utf
1/11/2010 3:10:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
3318 Views

Similar Articles

[PageSpeed] 8

Sub parens()
    Dim rng1 As Range
    Set rng1 = ActiveSheet.Range(Cells(1, 1), _
    Cells(Rows.Count, 1).End(xlUp))
    For Each cell In rng1
        If cell.Value <> "" Then
            cell.Value = "(" & cell.Value & ")"
        End If
    Next
End Sub



Gord Dibben  MS Excel MVP

On Mon, 11 Jan 2010 07:10:01 -0800, Deb <Deb@discussions.microsoft.com>
wrote:

>How do I auto-insert parethesis around prepopulated data in a worksheet? I 
>have a column of information that only if there is information, then I would 
>like it to be in parenthesis. For example, 
>
>A1: data
>B1: data
>C1: no data
>D1: no data
>E1: data
>
>I would like to auto-insert parenthesis around the cells that show "data" 
>but to ignore cells that have "no data". This would involve only 1 column in 
>the excel file. The cells that contain data would look like this, example: 
>(Smith) or (Jones) rather than Smith or Jones.

0
Gord
1/11/2010 4:47:33 PM
Thanks, Gord. This works excellent. Now if I could ask one more favor? How 
can I get this to work in a specific column? The data I am trying to 
manipulate is in column E. The current module wants to apply the info to 
column A. If this is an impossible request, I think I know a way to move the 
columns around so that the present module will work. Thank you so much.

"Gord Dibben" wrote:

> Sub parens()
>     Dim rng1 As Range
>     Set rng1 = ActiveSheet.Range(Cells(1, 1), _
>     Cells(Rows.Count, 1).End(xlUp))
>     For Each cell In rng1
>         If cell.Value <> "" Then
>             cell.Value = "(" & cell.Value & ")"
>         End If
>     Next
> End Sub
> 
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Mon, 11 Jan 2010 07:10:01 -0800, Deb <Deb@discussions.microsoft.com>
> wrote:
> 
> >How do I auto-insert parethesis around prepopulated data in a worksheet? I 
> >have a column of information that only if there is information, then I would 
> >like it to be in parenthesis. For example, 
> >
> >A1: data
> >B1: data
> >C1: no data
> >D1: no data
> >E1: data
> >
> >I would like to auto-insert parenthesis around the cells that show "data" 
> >but to ignore cells that have "no data". This would involve only 1 column in 
> >the excel file. The cells that contain data would look like this, example: 
> >(Smith) or (Jones) rather than Smith or Jones.
> 
> .
> 
0
Utf
1/12/2010 2:22:01 PM
You must change the column reference

See the pattern for  (row, column)

Sub parens()
   Dim rng1 As Range
   Set rng1 = ActiveSheet.Range(Cells(1, 5), _  ' row1, column5
     Cells(Rows.Count, 5).End(xlUp))  'column5
     For Each cell In rng1
         If cell.Value <> "" Then
             cell.Value = "(" & cell.Value & ")"
         End If
     Next
 End Sub

This would also work.

Sub parens()
     Dim rng1 As Range
     Set rng1 = ActiveSheet.Range(Cells(1, "E"), _
     Cells(Rows.Count, "E").End(xlUp))
     For Each cell In rng1
         If cell.Value <> "" Then
             cell.Value = "(" & cell.Value & ")"
         End If
     Next
 End Sub


Gord

On Tue, 12 Jan 2010 06:22:01 -0800, Deb <Deb@discussions.microsoft.com>
wrote:

>Thanks, Gord. This works excellent. Now if I could ask one more favor? How 
>can I get this to work in a specific column? The data I am trying to 
>manipulate is in column E. The current module wants to apply the info to 
>column A. If this is an impossible request, I think I know a way to move the 
>columns around so that the present module will work. Thank you so much.
>
>"Gord Dibben" wrote:
>
>> Sub parens()
>>     Dim rng1 As Range
>>     Set rng1 = ActiveSheet.Range(Cells(1, 1), _
>>     Cells(Rows.Count, 1).End(xlUp))
>>     For Each cell In rng1
>>         If cell.Value <> "" Then
>>             cell.Value = "(" & cell.Value & ")"
>>         End If
>>     Next
>> End Sub
>> 
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Mon, 11 Jan 2010 07:10:01 -0800, Deb <Deb@discussions.microsoft.com>
>> wrote:
>> 
>> >How do I auto-insert parethesis around prepopulated data in a worksheet? I 
>> >have a column of information that only if there is information, then I would 
>> >like it to be in parenthesis. For example, 
>> >
>> >A1: data
>> >B1: data
>> >C1: no data
>> >D1: no data
>> >E1: data
>> >
>> >I would like to auto-insert parenthesis around the cells that show "data" 
>> >but to ignore cells that have "no data". This would involve only 1 column in 
>> >the excel file. The cells that contain data would look like this, example: 
>> >(Smith) or (Jones) rather than Smith or Jones.
>> 
>> .
>> 

0
Gord
1/12/2010 10:47:57 PM
Reply:

Similar Artilces:

Auto Format
Can you create custom Auto Formats in Excel -- Message posted from http://www.ExcelForum.com ...

Inserted Pictures keep disappearing replaced w/ white box with a r
I have never had this problem in power point before. I opened an old presentation to update it (Professional Edition 2003). Photos I had inserted were gone and replaced with white squares with red Xs. I tried to replace the photos and saved it. When I went to reopen today, the photos are gone again. HELP! Also, when I open the folder and point to the file I want to open, the small "Details" icon still shows my photos as being there, but they're not there; when I try to print, it's the white square with the red x. Thanks for any support anyone can give me! ...

auto Figure numbering and updating
Hopefully this is the correct discussion group for my inquiry. I am a technical writer developing maintenance manuals for large machines. I use photos, graphics and drawings regularly in the manuals. They are always 'Figure X-X' numbered. Is there a way to make my document automatically number and update according to position in the document? i.e. adding or deleting a figure would automatically vs manually re-number accordingly. The Insert Caption facility does exactly that - insert captions with numbers that will automatically update if one of them is deleted are on...

Exchange 2000
Hi all I've recently renamed a user. Now I found that everytime I type that user name in the outlook, it shows me the name like this NewUserName <OldUserName>. I've checked all in the AD, but not found anythings related to the old user. How to correct it? Please help. Thanks BT Just a few points ... 1 - you can delete the names from the autocomplete by highlighting them (use the keyboards arrow keys) and hitting the DEL key. 2 - the <OldUserName> is displayed because that is the user's LegacyDN value. This value can be changed by using adsiedit however if...

how to -- exchange for auto send a cc copy to
Hi, I wonder who know how to config exchange for auto send a cc copy to administrator or director when mails send out from particular exchange user(s) without using outlook rule. Thanks It would have to be a rule in Outlook. What's your goal - to see all messages sent/received by/from a particular user? What version of Exchange, and is it Enteprise or Standard? triston wrote: > Hi, > > I wonder who know how to config exchange for auto send a cc copy to > administrator or director when mails send out from particular exchange > user(s) without using outlook rule. > ...

Inserting picture from "My Pictures"
When I add a pic from "My pictures" the entire picture does not appear on the slide and when I try to format it it is impossible to get it back to the same pic I am attempting to add. If I am trying to add a pic I might only get a nose or a blade of grass instead of the original picture from "My Pictures". Reduce the zoom to 10%. Coul be a too large image "PP Neophyte" <PP Neophyte@discussions.microsoft.com> a �crit dans le message de news: CA5F97E6-D4D8-401B-B066-E261024F082F@microsoft.com... > When I add a pic from "My pictures" t...

PA Purchase Receipt auto-Transfer to Project
When a PO is received for a project, it would be great to automatically transfer the inventory to the project. Also if a PO Return is entered related to a project, an automatic PA IV Tfr return document would be created. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane...

Auto-Editing
Word 2003 has a very annoying default. It assumes too much! If I reformat a few words in a document, it changes the entire document to that formatting. Grr-rrr-r! I have to constantly press undo to get what I want. I know there's a feature that's causing this annoyance but I can't find it to turn it off! Can anyone help, please? See http://word.mvps.org/faqs/formatting/wholedocumentreformatted.htm. -- Stefan Blom Microsoft Word MVP "Connie Martin" <ConnieMartin@discussions.microsoft.com> wrote in message news:CD959D82-F81B-4A9D-993E-73...

Insert a .gif animated file in a new mail in outlook 2007
how cn i set a animated .gif file in new mail in outlook 2007. "abc" <abc@discussions.microsoft.com> wrote in message news:2A924B58-51DE-4438-BD76-60FDBA119269@microsoft.com... > how cn i set a animated .gif file in new mail in outlook 2007. AFAIK you can't because (thank goodness) Outlook 2007 doesn't support animations. You can use the Insert image options but it will not be animated in Outlook. If the recipient uses Outlook they will need to view the message in a browser to see the animation. -- Diane Poremsky [MVP - Outlook] Outlook Tip...

VBA or Macro to auto fill a selected range
Hello everyone, Here is my problem. I have a spreadsheet with four columns, which are variables nested within each other. What I am currently doing is going through a range, finding the blank cells, filling all the blanks with the value of the non-blank cell above it, and filling these blanks until I reach a non-blank cell. Simply put, all I am doing is clicking on the cell's handle and filling the blanks (it only fills the cells down until it finds a cell with a value). I am tediously going through each range of cells, autofilling down, and working from right to left. There must...

Inserting system date stamp on html email signature
Front Page has a feature that will alow you to insert an automatically update sytem date that displays as text: January 9, 2004 <!--webbot bot="Timestamp" S-Type="REGENERATED" S-Format="% B %d, %Y" --> I cannot get this code to work in an Outlook HTML signature, nor can I find a way to do it with the Outlook editor. Anybody got any ideas for a workaround? Gary I'm no OL code expert, but I recently purchase a nifty little tool from Sperry Software for $9.95 that will insert the date and time pretty much anywhere in Outlook. You simply run the ins...

Fixed Asset
Hi, anyone out there figure out a way to add the Fixed Asset ID using a next number instead of the user having to go through the FA listing and look at the last number to setup the next number? Thanks, It's really a matter of writing a customization or purchasing a tool like EthoTech's Next Numeric Collection. -- Charles Allen, MVP "cal" wrote: > Hi, anyone out there figure out a way to add the Fixed Asset ID using a next > number instead of the user having to go through the FA listing and look at > the last number to setup the next number? > >...

Excel auto filter does not show all choices for the field?
I have a spreadsheet with client information in approx 3900 rows. Using auto filter to scroll through alphabetized last name field, the data ends part way through alphabet. Deleting that row, or a group of rows surrounding that one, doesn't change the result, it always stops at the same line number. This is a recent problem, it worked fine for two years! Jim, AutoFilter dropdown list will only show 1000 entries. Have a look here for details http://www.contextures.com/xlautofilter02.html#Limits -- Paul B Always backup your data before trying something new Please post any response to...

Auto-stop feature?
I would like to be able to play a DVD while I'm faling asleep and have Windows Media Player automatically stop at the end of the DVD (so the DVD drive doesn't run all night). I have the feature on my TV's DVD player, so I assume it is something that can also be done on my computer. I just can't figure out how. On Mon, 10 May 2010 19:00:01 -0700, hlagas <hlagas@discussions.microsoft.com> wrote: > >I would like to be able to play a DVD while I'm faling asleep and have >Windows Media Player automatically stop at the end of the DVD (so the DVD ...

Auto fill a column
I would like to create a macro that fills a column for me from the first cell. My dilemma is that the amount of rows of data are ever changing, and I would like the macro to fill until there is no more data. Any help would be appreciated. If I know my data, I can usually pick out a column that always has data in it if that row is used. If you can do the same, you could use code like: Option Explicit Sub testme() Dim wks As Worksheet Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks LastRow = .Cells(.Rows.Count, &q...

insert a new cell into an existing formula
I have a simple formula in excel that adds every 7th row for a total. When I add rows to this worksheet (in the center), the formula does not update to reflect the new cells. How do I get the SUM formula to update? A formula such =SUMPRODUCT((A1:A20)*(MOD(ROW(A1:A20),7)=0)) will reflect the changes if a row is inserted within the range. What is yours like? -- HTH RP (remove nothere from the email address if mailing direct) "Debbie" <Debbie@discussions.microsoft.com> wrote in message news:DE30DEA6-840E-48FB-B372-C9EC015E9258@microsoft.com... > I have a simple form...

Address Auto-Complete doesn't work right
Using Outlook 2002 sp2 (from Office XP Pro) on WinXP Home Most of the time the address auto-complete function does not operate correctly... If I have one person in the address named Adam and one named Alan and I beging typing 'ala' (no quotes), it presents me with Alan as the auto-complete entry. If I have Donna in my address book and I type 'donna ' I don't get presented with any choices or an auto-complete entry. I have 5 people with first names of Michael, and it chooses the first one in the list, but does not present the 4 others in a drop list. I have the Tools-&...

Auto Macros
is there anyway to autorun the macro as soon as the data is entere instead of pressing a button ?? -- cassy0 ----------------------------------------------------------------------- cassy01's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=78 View this thread: http://www.excelforum.com/showthread.php?threadid=26329 You can use the Worksheet_Change(ByVal Target As Range) Event as trigger to do processing, although I would need more information. Would it be a range of cells, if so, what range or column -- CBrin -------------------------------------------------...

Blue Screen upon USB Drive Insertion
I've been using this SwissMemory USB drive for a couple years now on the same computer system (an HP Pavillion notebook, Vista Ultimate 32-bit). As of just yesterday, I can no longer access the drive. As soon as I plug it in, I get a Blue Screen with the Stop Error 0x8E and my computer reboots. If I plug the drive in before windows finishes booting, I can access the drive just fine. However, anytime I plug the device in while Windows is already running, I immediately get the Blue Screen and a reboot. I've tried reformatting the flash drive, but it still happens. Any s...

Inserting a sub
Could anyone tell me how i can insert a sub in a excell sheet. I have found a sub, i copy the text and now i like to put it into excell so i can use it everytime. Not sure what you mean. A subroutine must go into a code modul contained in a workbook. With the workbook open use keys Alt +F11 t open the VB Editor. Use menu Insert/Module and copy/paste the code t there. If you want the code page for a specific worksheet, right clic the sheet tab and select "View Code" -- Message posted from http://www.ExcelForum.com Guido Depends upon what type of Sub you have copied. Event co...

Auto Open #2
Is it possible to have more than 1 Auto Open sub? combine -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Ed Davis" <ed.davis1@verizon.net> wrote in message news:A15C1D19-FDEC-471B-B745-C0DF7C9311A7@microsoft.com... > Is it possible to have more than 1 Auto Open sub? > Hi, Why would you want more than one? Your Workbook_Open subroutine can call as many subroutines as you want. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Ed Davis" wrote: > Is it possible to have more than 1 Auto O...

Auto configure outlook for exchange users
We use roaming profiles, along with outlook 2002 and exchange 2000. Is there a way to auto configure outlook for the users, instead of having to do them all individually? Many many thanks, I really need help with this. There is a way to do it using GPO's and scripts. But I use Scriptlogic logon scripts to configure the users profiles and settings for everything including Outlook. www.scriptlogic.com :-) >-----Original Message----- >We use roaming profiles, along with outlook 2002 and >exchange 2000. Is there a way to auto configure outlook >for the users, instead...

Customise Auto-reolution of users/addresses in GAL
Hi Situation Exchange Server 2003, User using the to: field to type only first names and let's Exchange resolve it. One of the users he often emails to is named John Wood Problem added new user Tim Johnson. When he types "John; Jeff..." John is not resolved to John Wood anymore but gets underlined in green. Customer finds it anoying to have to click the underlined user and select the right one... "If I want Mr johnson, I will type in Tim, but when I type John he does not have to think Johnson, but John".... Hard customer zo pls hel "ezjurgen" <anonymous...

How to insert a current date
To insert Current date help page shows: Select a cell and press CTRL+; This could be Select a cell and press CTRL+Semicolon(;) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=a107213f-e55a-4bd3-8e3...

settings for inserting Excel documents directly into Outlook
When utilizing Office 2002 with Outlook 2002, in Excel sending the Excel document to the Mail Recipient works okay, but the Excel document is oversized. The only way to get the document to be sized in a reasonable matter without taking up three pages is Send to Mail (As Attachment). How can you change the settings of the Send to Mail Recipient command? The first thought is it could be the document, and the answer is that this document is emailed to 10 locations and they all change some text and email it back. One location is having trouble. The Office was originally 2000, upgraded to 2002...