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
3320 Views

Similar Articles

[PageSpeed] 28

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:

Insert .psd in Publisher 2003
I'm trying to insert a logo that is a .psd into Publisher. When I hit insert, it asks me what I'm trying to convert from. What do I do? Is it possible to insert a photoshop file, or do I need to convert it first? You'll need to change the format of the Photoshop file before you insert it in your document. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "brenda" <brenda@discussions.microsoft.com> wrote in message news:7F8C6E5D-C83E-4FEC-92D8-CFA24A00AA92@microsoft.com... > I'm trying to ...

insert row when cell full, auto wrap
Hi there, I used this code in ver 2003 to insert a row and wrap text in a cell..BUT, it does not work in ver 2007. Any ideas? Much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = ...

Auto-Accept Agent event sink for Exchange Server 2003 help!
I have installed the Auot-Accept Agent, using the local system account as the service's account. I have edited the AutoAccept.config.xml file, leeaving only the "global" configuration information. I have run the RegisterMailbox.vbs and ListMailboxes.vbs scripts to add and list those accounts added (I created a text file with the default SMTP addresses as found in Exchange Server 2003). All of these steps appear to have gone off flawlessly... The problem... proving this is true with the MailboxStatus.vbs script. This script's XML output (run with the /DEBUG option on) indic...

ValidationType.Auto and reading schema attribute
Hi, I would like my default ValidationType to be Auto. Now let's say I have both a DTD and an XSD file for this XML file being validated. While it's ValidationType.Auto, it will validate the DTD, but will not even attempt to read/validate the namespace or even check if schema file exists. I suppose it's a resolver problem; anyone have any insight on how I can have it check the schema File while Auto (without setting ValidationType.Schema)? ..NET 1.1 Validation can not perform DTD and XSD validation at the same time. If you have ValidationType set to Auto, and have a DTD decl...

Ability to insert a row to the Payroll Quick Entry
I would like to make the following suggestion with respect to the Payroll Canada. I was wondering if the functionality in the Payroll Qiuck Entry could allow the user to insert a row when entering hours. Presently we have the ability to delete a row but not insert a row. If you are making adjustments to an individual employee you can't insert a row you have to do the adjustments at the bottom of the page. ---------------- 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&...

Cannot Insert Object
Hi all I'm having trouble inserting a .msg outlook file into an Excel workbook, can anyone please advise as to why I'm getting this error. Many thanks Badger -- BadgerMK ------------------------------------------------------------------------ BadgerMK's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31406 View this thread: http://www.excelforum.com/showthread.php?threadid=511023 ...

Can't open excel or word documents (says insert sm bus cd?)
I have been running Windows XP for over a year without a problem, but now I can not open any of my Excel or Word or Outlook documents because it says that I need to insert my small business cd. I don't have that cd. This has happened once before and I just used my system restore function and it was fine, but that won't even work this time. HELP!! ~~Angela @ angela27@iglide.net ...

how do you insert an element into an xml document with namespace
perfix? <bac:BACnetDevice APDUTimeout='' ApplicationSoftwareVersion='' FirmwareRevision='' InstanceNumber='3744' MaxAPDULengthAccepted='' ModelName='' NumberOfAPDURetries='' ProtocolConformanceClass='' ProtocolObjectTypesSupported='' ProtocolServicesSupported='' ProtocolVersion='' SegmentationSupported='' VendorIdentifier='' VendorName='' xmlns:bac='http://www.teletrol.com/BACnet'> <bac:BACnetPoints> <bac:baseAnalogInput EventState='0' Insta...

How do I insert a picture within an Excel comment box?
How do I insert a picture within an Excel comment box? There are instructions here: http://www.contextures.com/xlcomments02.html#Picture Roy White wrote: > How do I insert a picture within an Excel comment box? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Auto-Hide and Always On Top Plug In or Code?
I have developed a small app I would like to have behave like the Windows XP Taskbar, in that users should be able to select either Auto- Hide or Always On Top, and get the same behavior you get with the Taskbar. Is this something I should be able to accomplish in code? When I first started this project months ago, I didn't think so, so I convinced my firm to purchase LogicNP's Shell Mega Pack Sorry, too fast the fingers. Anyway, there are a number of issues with the ShellAppBar tool that make it undependable, and LogicNP seems to no longer be supporting the product (or...

Moving the email addresses from Auto-Complete
This question is Windows Vista/Windows 7 and Outlook 2007 specific. I am about to upgrade a machine running Windows Vista to Windows 7. How do I migrate the email adresses from Auto-Complete to the new machine. I cannot find the *.NK2 file anywhere on this machine. The resposes always seem to reference Win XP or Outlook 2003. I need an aswer for my specific question. "John A" <John A@discussions.microsoft.com> wrote in message news:08F25A1C-B461-4A02-8562-3E491553CA07@microsoft.com... > This question is Windows Vista/Windows 7 and Outlook 2007 specific....

what is the difference between auto fill and custom fill
I do not think "custom fill" is a truly valid term. Autofill is the process that uses data in adjoining cells to determine the contents of cells. This can be based on Excel's internal rules or the contents of Custom Lists. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Auto Fill/Auto Lookup??
I have created a form to input new data into the database. The data table for the database is called "TAODailyLog". In this table the column headings are Date, Site ID, Buoy ID, WMO ID, and Deploy Date (there are others, but these are the important ones). I have another table called "siteid". This table I use primarily for looking up values. In the "siteid" table, the columns are Order, SiteID, pmID, wmoID, and juliandeployed. With each new record that is placed into the database, the user must put in the Site ID, Buoy ID, WMO ID and the Deploy Date. ...

Auto Function
Is there a way tin access if a certain field in a table is something that it will automatically fill something in another field. If a value within a table is 90 to 95 I would like it to fill in the value of 4 and if it is 96 to 100 to fill in a value of 5. Is there anyway for this to be accomplished. Thanks On Wed, 12 Mar 2008 17:45:01 -0700, Anthony wrote: > Is there a way tin access if a certain field in a table is something that it > will automatically fill something in another field. If a value within a > table is 90 to 95 I would like it to fill in the value of 4 and ...

Inserting fractions
How do I format fractions (such 1/8, 2/3) that are not available under "insert/symbol"? I have tried using superscript and subscript on the denominators, but the fraction looks different? Any ideas? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Kristen <anonymous@discussions.microsoft.com>... > How do I format fractions (such 1/8, 2/3) that are not > available under "insert/symbol"? I have tried using > superscript and subscript on the denominators, but the > fraction looks different? Insert > Object > Microsoft Equ...

Text Not Inserting
Hi all, I am attempting to pass a large amount of text to a field when I close a form, however, the text is being cut off, usually ending in weird ASCII characters. It is random of when it happens, but it never puts in the full amount of text that is in the field. I am using MySQL tables, and the field is set to LONGTEXT. My code uses a VB INSERT INTO and works, except for the one field not capturing anything. I am not sure where to pursue this, but thought I would start here. Please let me know if more information is needed. Perhaps instead of saving to a table, would it be better t...

Auto Slate Excel File
I have a program call AutoSlate which tracks car maintenance fuel ect and for some reason it has stated putting ####### in the date field just certain ones not all the date field. If I click on the it will put the correct date in and when I save it it's fine. Though when I open the document back open I get the ##### in current date field again. I have also experience this in Documents to Go if I click on the ###### it will show in cell information the correct date but will not update it. Here is what I cut from the cells as you see it give the dates correctly but if you look at the docume...

making a dvd insert
how do i go about designing my own dvd insert.a wedding dvd? willieweir44 <willieweir44@discussions.microsoft.com> was very recently heard to utter: > how do i go about designing my own dvd insert.a wedding dvd? 1) Set up your page to be DVD-insert-sized 2) Add pictures, text, and other objects to your page. -- Ed Bennett - MVP Microsoft Publisher ...

One large file, broken into smaller files and inserted into a mast
My company has many programs with very large files. I have recommended breaking these files into smaller subfiles and inserting each of these sub files into a master so they can be accessed by more than one person. Server in on the horizon but not available yet. When this is done, the difficult area is to re-link the tasks now between sub projects that were originally linked together while in the one file. Do anyone have a macro, checklist, process, that can help expedite this and check to ensure the subproject are relinked to the same task as they were before the split? ...

Inserting text within a cell
I would like to insert a , with a cell. i have Wood T 3,1 i would like to have Wood T,3/1 Sorry it should read I have Woods T 3/1 i want Woods T, 3/1 Thank you "Hayley" wrote: > I would like to insert a , with a cell. > > i have Wood T 3,1 > > i would like to have > Wood T,3/1 If cell A1 contains (surname)(single space)(single initial)(single space)(score), the following will achieve what you want: =LEFT(A1,FIND(" ",A1)+1)&","&RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(" ",A1)+1))) HTH "Hayley" wrote: >...

Auto Archive #2
Auto Archive has requested to archive my Outlook folders, I click OK but when checking the Archive section I see no e-mail. The policies are to delete only items older than 12 mos. and the archive.pst file is in the proper place as listed. It is importsnt that these messages be available. Thanks Have you checked to make sure the messages are actually being removed from your primary Outlook file? If they're not, then AutoArchive isn't doing anything to them at all. Check the Modified date on the items that aren't being archived as you think they should. That's the date Ou...

Insert a Formula in a TextBox
Dear all, I would like to insert a formula in my TextBox in the moment I initialize my UserForm: Private Sub UserForm_Initialize() With cboDivision .AddItem "HE" .AddItem "IT" .AddItem "IC" .AddItem "CO" End With txtDate.Text =3D FormulaR1C1 =3D _ "=3DYEAR(TODAY())&IF(LEN(MONTH(TODAY()))=3D1,0&MONTH(TODAY()),MONTH (TODAY()))&IF(LEN(DAY(TODAY()))=3D1,0&DAY(TODAY()),DAY(TODAY()))" End Sub Is it possible? Thanks in advance! Andr=E9 txtDate.Text = Format(Date, "yyyymmdd") -- _____________...

Office stopped working after the auto-update a few months ago
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Microsoft Office stopped working after it ran an auto-update a few months ago. Now, when I open any of the Office apps, I get an error message like this: &quot;Check with the developer to make sure Microsoft Word works with this version of Mac OS X. You may need to reinstall the application. Be sure to install any available updates for the application and Mac OS X.&quot; <br><br>There are no available updates for Mac OS X or Office that I am aware of�when I run the Software Update it doesn&#...

Can't insert pictures - used to be able to
Had to reload XP and then reload Office 2000 Pub 2000. Since reload can't insert pictures into Publisher. Get error "Publisher cannot convert this picture....." Have tried all kinds of formats - jpg, tif, gif, pcx, even bmp. None will load. I've reloaded Publisher and made sure that all graphics converts are loaded. Anyone have any ideas? It used to work just fine. How large is this picture? "djg in Ohio" <anonymous@discussions.microsoft.com> wrote in message news:17da801c4199c$ddfeea80$a401280a@phx.gbl... > Had to reload XP and then reloa...

"There Is No Disk in the Drive. Please Insert a Disk into Drive \Device\harddisk\Dr6" Error
OS: WinXP Home SP3 I've just started to get this error message randomly (sometimes when opening an application or opening Windows Explorer, etc...: "There Is No Disk in the Drive. Please Insert a Disk into Drive \Device\harddisk\Dr6" Any ideas? Thanks for your time. -- JamesKB On May 17, 1:40=A0pm, "JamesKB" <JimSPAM...@NOTSPAMres-xraynews.com> wrote: > OS: WinXP Home SP3 > > I've just started to get this error message randomly (sometimes when open= ing > an application or opening Windows Explorer, etc...: > > &...