IF statements #5

In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
create an IF statement that says if there is a text value in the cell put 
parentheses around that value and if there is nothing in the cell do nothing?

Any help would be appreciated
0
6/28/2007 4:46:03 PM
excel.newusers 15348 articles. 2 followers. Follow

9 Replies
426 Views

Similar Articles

[PageSpeed] 37

=IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.


Gord Dibben  MS Excel MVP


On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
<ExcelFunctionApplication@discussions.microsoft.com> wrote:

>In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
>create an IF statement that says if there is a text value in the cell put 
>parentheses around that value and if there is nothing in the cell do nothing?
>
>Any help would be appreciated

0
Gord
6/28/2007 5:17:14 PM
Since you only have 3 values, I think the easiest way to do this would be 
using FIND/REPLACE.

Select your range of data
From the Edit Menu, choose "Replace"
In the Find What field enter: JR.
In the Replace With field enter: (JR.)
Click "Replace All"
Repeat for CPA and MD

HTH,
Elkar


"Excel Function Application" wrote:

> In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
> create an IF statement that says if there is a text value in the cell put 
> parentheses around that value and if there is nothing in the cell do nothing?
> 
> Any help would be appreciated
0
Elkar (111)
6/28/2007 5:18:07 PM
Thank you very much Gord.  I have several lists with different text entries 
but thousands of rows...this is really going to help!

"Gord Dibben" wrote:

> =IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> 
> On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
> 
> >In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
> >create an IF statement that says if there is a text value in the cell put 
> >parentheses around that value and if there is nothing in the cell do nothing?
> >
> >Any help would be appreciated
> 
> 
0
6/28/2007 6:14:02 PM
Don't forget that you can just double-click on the fill handle of B1 to copy
down to end of data in column A.


Gord

On Thu, 28 Jun 2007 11:14:02 -0700, Excel Function Application
<ExcelFunctionApplication@discussions.microsoft.com> wrote:

>Thank you very much Gord.  I have several lists with different text entries 
>but thousands of rows...this is really going to help!
>
>"Gord Dibben" wrote:
>
>> =IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> 
>> On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
>> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>> 
>> >In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
>> >create an IF statement that says if there is a text value in the cell put 
>> >parentheses around that value and if there is nothing in the cell do nothing?
>> >
>> >Any help would be appreciated
>> 
>> 

0
Gord
6/28/2007 6:55:16 PM
Again, very helpful.

This is along the same lines so the formula is probably going to be 
structured the same way.  If you have time that would be great.

I need to get rid of the symbols in one column.  I want only text.  I have 
tried the edit > replace route but that leave me with un unnecessary space 
where the period used to be.

i.e.

column A       column B
..adams         adams
johnson        johnson
smith            smith
..ward           ward
wilson          wilson

is this possible with an IF statement?

"Gord Dibben" wrote:

> Don't forget that you can just double-click on the fill handle of B1 to copy
> down to end of data in column A.
> 
> 
> Gord
> 
> On Thu, 28 Jun 2007 11:14:02 -0700, Excel Function Application
> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
> 
> >Thank you very much Gord.  I have several lists with different text entries 
> >but thousands of rows...this is really going to help!
> >
> >"Gord Dibben" wrote:
> >
> >> =IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.
> >> 
> >> 
> >> Gord Dibben  MS Excel MVP
> >> 
> >> 
> >> On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
> >> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
> >> 
> >> >In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
> >> >create an IF statement that says if there is a text value in the cell put 
> >> >parentheses around that value and if there is nothing in the cell do nothing?
> >> >
> >> >Any help would be appreciated
> >> 
> >> 
> 
> 
0
6/28/2007 7:32:03 PM
If you want column A retained as is..................

Copy A to column B then do your edit>replace on B only.

With your sample data, if you replace the dot(.) with nothing you should not
have any spaces.

Otherwise just do the edit>replace on column A and dispense with B

If  the dot was just a general sample, what other symbols do you need removed?


Gord


On Thu, 28 Jun 2007 12:32:03 -0700, Excel Function Application
<ExcelFunctionApplication@discussions.microsoft.com> wrote:

>Again, very helpful.
>
>This is along the same lines so the formula is probably going to be 
>structured the same way.  If you have time that would be great.
>
>I need to get rid of the symbols in one column.  I want only text.  I have 
>tried the edit > replace route but that leave me with un unnecessary space 
>where the period used to be.
>
>i.e.
>
>column A       column B
>.adams         adams
>johnson        johnson
>smith            smith
>.ward           ward
>wilson          wilson
>
>is this possible with an IF statement?
>
>"Gord Dibben" wrote:
>
>> Don't forget that you can just double-click on the fill handle of B1 to copy
>> down to end of data in column A.
>> 
>> 
>> Gord
>> 
>> On Thu, 28 Jun 2007 11:14:02 -0700, Excel Function Application
>> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>> 
>> >Thank you very much Gord.  I have several lists with different text entries 
>> >but thousands of rows...this is really going to help!
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> =IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.
>> >> 
>> >> 
>> >> Gord Dibben  MS Excel MVP
>> >> 
>> >> 
>> >> On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
>> >> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>> >> 
>> >> >In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
>> >> >create an IF statement that says if there is a text value in the cell put 
>> >> >parentheses around that value and if there is nothing in the cell do nothing?
>> >> >
>> >> >Any help would be appreciated
>> >> 
>> >> 
>> 
>> 

0
Gord
6/28/2007 7:52:24 PM
Shutttt uuup!  Thanks, Gord

Linda

"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:5v0883prfdft9b756mao2dmjsdfhh0dirq@4ax.com...
> Don't forget that you can just double-click on the fill handle of B1 to 
> copy
> down to end of data in column A.
>
>
> Gord
>
> On Thu, 28 Jun 2007 11:14:02 -0700, Excel Function Application
> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>
>>Thank you very much Gord.  I have several lists with different text 
>>entries
>>but thousands of rows...this is really going to help!
>>
>>"Gord Dibben" wrote:
>>
>>> =IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.
>>>
>>>
>>> Gord Dibben  MS Excel MVP
>>>
>>>
>>> On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
>>> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>>>
>>> >In column A there are either values of "JR.", "CPA", "MD", or nothing. 
>>> >Can I
>>> >create an IF statement that says if there is a text value in the cell 
>>> >put
>>> >parentheses around that value and if there is nothing in the cell do 
>>> >nothing?
>>> >
>>> >Any help would be appreciated
>>>
>>>
> 


0
Linda1 (5)
6/28/2007 11:55:29 PM
You're welcome...........I think<g>

Gord

On Thu, 28 Jun 2007 19:55:29 -0400, "Linda \(RQ\)" <Linda@TenForward.Laptop>
wrote:

>Shutttt uuup!  Thanks, Gord
>
>Linda
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:5v0883prfdft9b756mao2dmjsdfhh0dirq@4ax.com...
>> Don't forget that you can just double-click on the fill handle of B1 to 
>> copy
>> down to end of data in column A.
>>
>>
>> Gord
>>
>> On Thu, 28 Jun 2007 11:14:02 -0700, Excel Function Application
>> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>>
>>>Thank you very much Gord.  I have several lists with different text 
>>>entries
>>>but thousands of rows...this is really going to help!
>>>
>>>"Gord Dibben" wrote:
>>>
>>>> =IF(A1<>"","(" & A1 &")","")  entered in B1 and copied down.
>>>>
>>>>
>>>> Gord Dibben  MS Excel MVP
>>>>
>>>>
>>>> On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application
>>>> <ExcelFunctionApplication@discussions.microsoft.com> wrote:
>>>>
>>>> >In column A there are either values of "JR.", "CPA", "MD", or nothing. 
>>>> >Can I
>>>> >create an IF statement that says if there is a text value in the cell 
>>>> >put
>>>> >parentheses around that value and if there is nothing in the cell do 
>>>> >nothing?
>>>> >
>>>> >Any help would be appreciated
>>>>
>>>>
>> 
>

0
Gord
6/29/2007 12:49:36 AM
one way...

=SUBSTITUTE(A1,A1,"("&A1&")")


"Excel Function Application" wrote:

> In column A there are either values of "JR.", "CPA", "MD", or nothing.  Can I 
> create an IF statement that says if there is a text value in the cell put 
> parentheses around that value and if there is nothing in the cell do nothing?
> 
> Any help would be appreciated
0
6/30/2007 1:54:01 PM
Reply:

Similar Artilces:

Arcserve 11.5 failed to backup two mailboxes out of 103
We use ArcServe 11.5 to do a document level backup of our Exchange 2003 mailboxes. When the application ran for the first time last night on the production server, after testing on a test server, the notification I recieved was that the backup job was incomplete. It failed to backup two mailboxes out of 103. How can I determine which mailboxes were not backed up and more importantly, why they were not backed up. I am not sure if this is an Exchange 2003 issue or an ArcServe issue. My inclination is that it is an Exchange 2003 issue because it did successfully back up the other 1...

Exchange 5.5 database is becoming too large
Hi, we have a server with Windows NT 4.0 SBS and Exchange Server 5.5. The server has a 3 Disks Raid 5 array. The array is divided in 6 partitions of about 6 GB (exept drive C that is 2 GB) Exchange is installed in drive D. Our exchange Database is now growed and is, at the moment about 5 GB. We have about 300 MB free on drive D. But we have more than 4 GB free on drive G. What can i do in order to avoid problems in future? Is it possible to put some mailboxes on another drive? It could be safe to work with partition magic in order to join some drives? Any other ideas? Please help us!!!! M...

Exchange 5.5 On W2K Services Won't Initialize
I have a Windows 2000 AD Server. Which has Exchange 5.5 installed on it. For some reason now the following services won't start. Exchange Event Service Internet Mail Service Message Transfer Agent System Attendant When I do try and start these services this error comes up: The application failed to initialize properly (0xc000007b) Click on OK to terminate the application. Then when I do this one comes up. Error 1053, the service did not respond to the start or control request in a timely fashion. In the event logs I get this as well Source:...

Printing Statements?
I'm a new user. Printing my first statements. How Do I get rid of the Minimum Payment field or change the amount to show the current balance? -- JD Hi JD - Thanks for your note. In order to modify the account statement, you will need to modify the XML file. The file name and path can be found in Store Operations Administrator, in the Configuration dialog, Templates tab. Typically, the file is Statement.XML and is located in c:\program files\microsoft retail management system\store operations\receipt templates folder. Once you find the file used for your account statements, locate...

a long thread of if-then statements
Hi. I'm using MS Excel Xp. Here's my problem: :confused: I have a large flatfile of our sales people and I need to do a pivot table on them. but before that, I need to create a new column which contains certain tiering of their earnings. For example: Name: Earnings Tier Person A $5000 $0 - $10,000 Person B $34,500 $30,001 - $40,000 Person C $1M $990,000 - $1,000,000 Person D $2.5M $2,490,001 - $2,500,000 The column "Tier" is the one I need to create. How do I create a long IF-TH...

Can't track message from outside to inside after enable message tracking on exchange 5.5 #3
Dear, I am using a Exchange 5.5 SP4 on Window 2000 SP4 We would want to track all message for debug use. So, I have enable the message Tracking. After enable it, I find that it can track the information for send mail between internal and send mail from internal to external But can't tray the information from outside to internal mailbox. I have do below setting on my exchange server, Is there any missing on the setting. Hope anyone can give me some help 1) Information Store Site Configuration Properties --> General --> tick Enable message tracking box 2) MTA Site Configuration Prop...

Business Portal 2.5 installation error "Unable to validate company information in your backoffice data."
hi, i got this message Unable to validate company information in your backoffice data upon installing Business Postal 2.5 Please help.. tnx Stephen, Thanks for using the newsgroups. The error you got generally means there is a data issue with either the DYNAMICS..SY01500 or DYNAMICS..DB_Upgrade tables. Verify that all companies listed in those tables are still valid in your environment. Thanks, Rob -- Rob Bernhardt Software Quality Assurance Microsoft Business Solutions This posting is provided "AS IS" with no warranties, and confers no rights. "Stephen Lee Glo...

Investment Accounts #5
My wife and I each have opened investment accounts at our local bank. So we have "BANK INVEST ACT#1" and "BANK INVESTACT #2" set up in Money for these accounts, #1 for my account user name and password with the bank and #2 for hers. When I update my accounts, that bank's retail accounts (checking/savings) update with no problem, but I get the following message on the Investing Accounts: Microsoft Money could not update your account. Please try to update your account again. If the problem continues contact customer support." I have checked and rechecked ...

Mirgration of 5.5 to 2003
I am currently migrating my 5.5 to 2003 exchange and I keep getting the following event log messages: Event Type: Warning Event Source: MSExchangeMTA Event Category: X.400 Service Event ID: 290 Date: 06/02/2006 Time: 13:11:06 User: N/A Computer: SUTSFS3 Description: A non-delivery report (reason code unable-to-transfer and diagnostic code loop-detected) is being generated for message C=US;A= ;P=CentLawTraining;L= {FCBACDA9-B7B0-4-060206131056Z-7. It was originally destined for DN:CN=TEST, CN=RECIPIENTS,OU=CLT,O=CENTLAWTRAINING§ (recipient number 1), and was to be redirected to . [...

Cell reference #5
What technique is used by excel for cell referencing? Microsoft excel uses absolute references -- _______________________ Click "Yes" button if it helps ________ Thanks Suleman Peerzade "Vick" wrote: > What technique is used by excel for cell referencing? Thankyou sir "Suleman Peerzade" wrote: > Microsoft excel uses absolute references > -- > _______________________ > Click "Yes" button if it helps > ________ > Thanks > Suleman Peerzade > > > "Vick" wrote: > > > What technique is used by exc...

/removeorg will it affect my 5.5 servers?
I have recently installed Exchange 2003 into my exchange 5.5 site, I now need to remove the 2003 server as it was only a test. Can I use the /removeorg to remove the exchange 2003 data from my active directory without it affecting my current 5.5 site. Thanks in advance. Lee. Lee Too late now but you shouldn't mess with your production environment in this manner. Get yourself a copy of MS Virtual Server or VMware and setup a lab. Did you set up the ADC connector as well? If so remove this and make sure your environment is stable before proceeding. In theory the /removeorg should h...

rank #5
Cell A2 needs to rank formula looks like this =RANK(K2,K:K,0) Range K:K is defined as "Total" the problem is every month, a new column is added in between (months) this pushes total to next column ( say L, M , N) thus the formula wont work in a months time.. also when the formula is copied down.. using a macro.. The last row also contains Grand Total. Thus that row is RANK 1... (because its total). Is there a macro to avoid this.? Its copies down... I suppose you mean =RANK(A2,K:K,0) When you insert columns, the formula will be changed automatically To avoid looking ...

Advanced month deposit should be shown in the statement after PTR
Posting for Kenny Wu Of Professional Advantage PTY LTD. Details: After running PTR on a given cut-off date (e.g. 8/31/2007). Transaction's Apply date field like 9/31/2007 should still be shown in the RM statement eventhough you have posted them on or before the PTR's cut-off date. ---------------- 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-...

If Statements #3
Hi all I am new to this, can someone, please help me with this. I wont to now how to evaluate two or more logical conditions if they are true or false using if functions for example. If total purchases in A2 are greater than $7000.00 deduct 15% from those purchases and if purchases are greater than $5000.00 deduct 10% of purchases otherwise purchases stay the same. How would I do this if funtion, Please, maybe use a or somehow? Cheers Mark -- burkey ------------------------------------------------------------------------ burkey's Profile: http://www.excelforum.com/member.php?actio...

If Statements in Access
Hello I am currently using the following IF statement in Excel however i will need to convert it so that it will work for me in Access =IF(Expiry Date<>"",IF(Expiry Date <Todays Date,"Cease Working ",IF(todays date> Expiry Date -60,"Submit New Application"," ")),"") Can someone please help Thanks Longbeach LongBeach, I take it you're updating a text control with text according to the value of [Expiry Date] vs. the current system Date... And, you don't need the If [Expiry Date] <> "". If it's...

Outlook newsreader #5
The following was sent a little while ago - because of the problem mentioned, I've missed any replies. Here it is again, Thanks in advance. D. Can anyone help me? My new pc setup will only show approx the last 5 items (headers) when I go to my regular news group, <uk.rec.skydiving> Can any one give me an idea on how to change which settings. I appear to have all settings correct for "all headers" etc. Thanks in advance, Dave. This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support tr...

Alert when consecutive numbers in a list exceed 5
Good morning I have thousands of records, and need to be alerted when, consecutive numbers (in a particular column) exceed 5. It has to be consecutive numbers (not sorted). eg data 1 data 1 data 8 data 8 data 8 data 8 data 8 data 8 In this case the number 8 exceeds 5. Would really appreciate help on this, as at the moment it is really time consuming. Thanking you in anticipation. Have you thought about using conditional formatting for this? Select the cells with the numbers you want to compare. Format -> Conditional Formatting Condition 1 -> For...

E2k3
Hi all! I've strange trouble with Exch 2k3. POP3 works fine, there's no problem. However, SMTP clients cannot send mail. On the server, SMTP is cinfigured to allow relay for authenticated clients only. On the clients (all I have seen), the authentication is enabled. However, server returns: ----- Outlook Express error ----- The message could not be sent because one of the recipients was rejected by the server. The rejected e-mail address was 'vojtek@emwacgroup.cz'. Subject 'Test mail...', Account: 'Test', Server: 'xxx.xxx.xxx.xxx', Protocol: SMTP...

Mailbox Manager for 5.5
I am trying to use the Mailbox Manager to clean the mailboxes in my organization. It works the very first time after installation, but then either through manually starting the clean or using the scheduled scan method, it doesn't work. It runs, but it says that it processed 0 mailboxes. After uninstall and re-install of Mailbox Manager, it works again the first time, but then doesn't work again. I'm using Microsoft Exchange Server 5.5 SP4 and have installed the MM from SP4 CD. Make sure you don't have Outlook on the server. It has been known that running Outlook (o...

smtp queues #5
I have exchange 2000. Recently we have had some users complaining that when they send emails the recepient either dont receive it or receive it very late. We do not have problems with any other emails and all incoming emails are coming in and going out ok. I have had a look in the smtp queues. and in the folder 'SMTP (SMTP Connector - Remote Delivery) the are messages there, after i enumerate 100 messages. One of the message i deleted with NDR and reading the ndr message ' This message was rejected due to the current administrative policy by the destination server. Please retry ...

how to put up 5 PCs with Windows XP and 7?
Hi, I have 3 PCs with Windows XP and 2 windows pc 7. I find it difficult to network to share folders between them. How can I network? thanks ... ...

Collections Management Default Statement ID
In GP 10, we've assigned a Default Statement ID in the Collections Management Local Setup window. However, when we go to the Collections Management Print Selection window and put a checkmark beside Print Statement, the Statement ID is not defaulting in. This worked in GP 9. Did this break in GP 10? Or, are we missing something? ...

Outlook 2003 BCM and Exchange 5.5
Is Outlook 2003 BCM compatible with Exchange 5.5..? I installed it as an add on...and it doens't work. Any ideas/advice would be helpful. Thanx in advance. No, BCM doesn't work with Exchange. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Jim C" <anonymous@discussions.microsoft.com> wrote in message news:ab0701c3b85a$d85fcbb0$a601280a@phx.gbl... > Is Outlook 2003 BCM compatible with Exch...

IF Statement....If False how ignore referenced cell
Hi, Is there a way using a "IF" statement that when the statement is false it ignores the cell it is writing to. For example: If(A1=1,B16,ignore) When it looks at cell A1 it will write what is in cell B16 (let's say 7). Next I want to change cell A1 to a 2 (because my value in B16 has changed), which makes the original IF statement False and typically overwrites the value in the cell with False, " " or "0". I would like it when FALSE, to leave the value 7 and not overwrite it.. Hope this makes sense. Any help would be greatly appreciated. Hi th...

help with IF THEN #5
What is the correct syntax to write a statement that, in english, does this If cell L4 = 0 Then delete cells K4 through M4 and shift the cells up Better yet can something be written that checks all L cells for the 0 value and if it finds 0 then deletes the adjacent K through M cells and shifts all cells up TIA ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...