Help replacing text with Yes or No

I have a field formated as general.  The field contains either 1 or is left 
blank.  If the field has a 1 I want to replace it with Yes and if the field 
is blank I want to replace it with No.

any help is appreciated.
-- 
Jerry 
0
jarnone (9)
6/23/2005 11:56:06 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
625 Views

Similar Articles

[PageSpeed] 15

Save your data and use a copy for this exercize...........

Assuming your data in Column A, put this in B1 and copy down........

=IF(A1=1,"Yes","No")

Then highlight the column and do Copy > PasteSpecial > Values to get rid of
the formulas..........then delete column A if you wish.......

Vaya con Dios,
Chuck, CABGx3



"Jerry Arnone, PMP" <jarnone@bellsouth.net> wrote in message
news:8411F5E3-3FF7-4B2B-83E4-C8EA3F831D12@microsoft.com...
> I have a field formated as general.  The field contains either 1 or is
left
> blank.  If the field has a 1 I want to replace it with Yes and if the
field
> is blank I want to replace it with No.
>
> any help is appreciated.
> --
> Jerry


0
croberts (1377)
6/24/2005 12:01:31 AM
....more options:

=IF(A1,"Yes","No")

=CHOOSE(A1+1,"No","Yes")

Regards,
KL


"CLR" <croberts@tampabay.rr.com> wrote in message 
news:un6$S$EeFHA.3808@TK2MSFTNGP14.phx.gbl...
> Save your data and use a copy for this exercize...........
>
> Assuming your data in Column A, put this in B1 and copy down........
>
> =IF(A1=1,"Yes","No")
>
> Then highlight the column and do Copy > PasteSpecial > Values to get rid 
> of
> the formulas..........then delete column A if you wish.......
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Jerry Arnone, PMP" <jarnone@bellsouth.net> wrote in message
> news:8411F5E3-3FF7-4B2B-83E4-C8EA3F831D12@microsoft.com...
>> I have a field formated as general.  The field contains either 1 or is
> left
>> blank.  If the field has a 1 I want to replace it with Yes and if the
> field
>> is blank I want to replace it with No.
>>
>> any help is appreciated.
>> --
>> Jerry
>
> 


0
6/24/2005 12:07:06 AM
the formula worked great but I need to keep the results in the A column.
-- 
Jerry Arnone
PMP, IT PROJECT+, CCNA, MCSE, CCA, CNA,
A+, SECURITY+, MS Project White Belt
JARNONE@BELLSOUTH.NET



"CLR" wrote:

> Save your data and use a copy for this exercize...........
> 
> Assuming your data in Column A, put this in B1 and copy down........
> 
> =IF(A1=1,"Yes","No")
> 
> Then highlight the column and do Copy > PasteSpecial > Values to get rid of
> the formulas..........then delete column A if you wish.......
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> 
> "Jerry Arnone, PMP" <jarnone@bellsouth.net> wrote in message
> news:8411F5E3-3FF7-4B2B-83E4-C8EA3F831D12@microsoft.com...
> > I have a field formated as general.  The field contains either 1 or is
> left
> > blank.  If the field has a 1 I want to replace it with Yes and if the
> field
> > is blank I want to replace it with No.
> >
> > any help is appreciated.
> > --
> > Jerry
> 
> 
> 
0
jarnone (9)
6/24/2005 12:20:02 AM
I got it...thanks everyone...worked great.
-- 
Jerry Arnone
PMP, IT PROJECT+, CCNA, MCSE, CCA, CNA,
A+, SECURITY+, MS Project White Belt
JARNONE@BELLSOUTH.NET



"CLR" wrote:

> Save your data and use a copy for this exercize...........
> 
> Assuming your data in Column A, put this in B1 and copy down........
> 
> =IF(A1=1,"Yes","No")
> 
> Then highlight the column and do Copy > PasteSpecial > Values to get rid of
> the formulas..........then delete column A if you wish.......
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> 
> "Jerry Arnone, PMP" <jarnone@bellsouth.net> wrote in message
> news:8411F5E3-3FF7-4B2B-83E4-C8EA3F831D12@microsoft.com...
> > I have a field formated as general.  The field contains either 1 or is
> left
> > blank.  If the field has a 1 I want to replace it with Yes and if the
> field
> > is blank I want to replace it with No.
> >
> > any help is appreciated.
> > --
> > Jerry
> 
> 
> 
0
jarnone (9)
6/24/2005 12:22:02 AM
You're very welcome........thanks for the feedback.........

Vaya con Dios,
Chuck=SUM(091938,USMCe4,CABGx3,MMOUS2k)


"Jerry Arnone, PMP" <jarnone@bellsouth.net> wrote in message
news:9A87E3BE-B755-41CD-B60B-444CFE88EE73@microsoft.com...
> I got it...thanks everyone...worked great.
> --
> Jerry Arnone
> PMP, IT PROJECT+, CCNA, MCSE, CCA, CNA,
> A+, SECURITY+, MS Project White Belt
> JARNONE@BELLSOUTH.NET
>
>
>
> "CLR" wrote:
>
> > Save your data and use a copy for this exercize...........
> >
> > Assuming your data in Column A, put this in B1 and copy down........
> >
> > =IF(A1=1,"Yes","No")
> >
> > Then highlight the column and do Copy > PasteSpecial > Values to get rid
of
> > the formulas..........then delete column A if you wish.......
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Jerry Arnone, PMP" <jarnone@bellsouth.net> wrote in message
> > news:8411F5E3-3FF7-4B2B-83E4-C8EA3F831D12@microsoft.com...
> > > I have a field formated as general.  The field contains either 1 or is
> > left
> > > blank.  If the field has a 1 I want to replace it with Yes and if the
> > field
> > > is blank I want to replace it with No.
> > >
> > > any help is appreciated.
> > > --
> > > Jerry
> >
> >
> >


0
croberts (1377)
6/24/2005 12:29:00 AM
How about just selecting the column and doing:

Edit|replace
what: 1
with: Yes
(check match entire cell contents under the Options button)
replace all

Edit|replace
what: (leave blank)
with: No
replace all

The blank cells in the used range will be changed.

Jerry Arnone, PMP wrote:
> 
> I have a field formated as general.  The field contains either 1 or is left
> blank.  If the field has a 1 I want to replace it with Yes and if the field
> is blank I want to replace it with No.
> 
> any help is appreciated.
> --
> Jerry

-- 

Dave Peterson
0
ec357201 (5290)
6/24/2005 1:05:39 AM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Provide space in text
Where text and numbers are at different locations in different cells what formula could be used to suit this type of information. I want to be able to put a space between the main body of text on the left and give a space between the text and the start of any numbers are in the cell. Example 1: A1 = Abelia Edward Goucher2 litre Should be: Abelia Edward Goucher 2 litre Example 2: A2 = Acer campestre Nanum180stem 6-8 Should be: Acer campestre Nanum 180stem 6-8 Thankyou if can be of help. Pat If you double clcik in the cell with the data then you can move th blink line to were you wan...

Halfway through my publisher document the text starts scrambling
I am in the middle of a Publisher document that I have to get out ASAP and the words are scrmbling all of a sudden; for instance instead of the it prints out eht. HELP!! Might try an updated printer driver. What version Publisher are you using? What printer? What version Windows? Canon has issues with Publisher 98 and Windows 98 How to troubleshoot text printing (inkjet printers) in Publisher 2000 http://support.microsoft.com/default.aspx?scid=kb;en-us;198258&Product=pub PUB2000: Troubleshooting Text Printing (Laser Printers) http://support.microsoft.com/default.aspx?scid=kb;en-us;198...

Exchange 2003 SMTP QUIT
= = = = = = = = = = = = = = = = = = = = = = = = = = = PROBLEM: Problem is that OUR SERVER is sending QUIT-, instead of sending MAIL FROM: MY Server open a SMTP connection REMOTE Server says 220 .. MY Server says EHLO to REMOTE Server REMOTE Server says 250 ... MY Server then say QUIT ! (instead of MAIL FROM ....) We have::Exchange 2003 , cu SP1, pe Windows 2003.. Exchange has also IMF (spam filter from Microsoft) and Symantec Mail Security for Exchange 4.5. all PTR is installed and working OK. The SMTP Server is workin OK a while, then it start opening a lot of connections (7-10 /sec) t...

GENERATE a TEXT FILE Using INPUT from a Form ??
I need to generate a Text file using (partly) information entered by the user on a form. I already have the TEMPLATE for the text file......I need to generate a COPY of the file with the word entered by the user into the form INSERTED into the text file. The text file is actually code for a web page (will have an .htm extension). The file is simply an exact copy of an existing template with only ONE ITEM of the text changed to a word that is entered into a form by a user. Example: One line within the template file is: "We have several of these items available in BLUE." I have...

I need help
Here is my problem. A couple of years ago I began playing around with money. But I have come to the conclusion that I really do like the services, but I now have a bunch of acct. info that should have been input in a better manner. I want to know how do I delete all of the old info so that I can start fresh. I deleted that money program once and reloaded it but it retrieved all of the old garbage that I did not want. Your help is greatly appreciated. Sincerly, Matt On Tue, 19 Aug 2003 20:58:14 -0700, "Matt" <mrisher4@hotmail.com> wrote: >Here is my problem. A c...

sort special text/numbers in format with many dots
Hi I need your help with sorting in Excel! I have mani Text fields with numbers into it. As example: 1 5.1 1.2 10.2.1 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 And it should sorted like this 1 1.2 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 5.1 10.2.1 How can I sort this like numbers? My problem is, that not all Numbers have the same format as x.x.x.x! And I can't change this Text-Fields to Numbers, because 10.6.1 looks the like 37052 :-( Any suggestions? Thx Marco Hi With your data in column A, insert a blank column at B. In B1 enter =IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".&q...

Help styles
With great difficulty I am now creating documents with some styles.and outline numbering Now when I copy a table from another document into this document I get 1.1.1.1 and similar numbering all over the table. How do I accomplish the copy without the numbering inside the table? afd Try this: Select the table cells and press Ctrl+Shift+N. This (re-)applies the Normal style to text. -- Stefan Blom Microsoft Word MVP "afdmello" <afdmello@hotmail.com> wrote in message news:%23R7DwkCdKHA.4952@TK2MSFTNGP06.phx.gbl... > With great difficulty I a...

full-text indexing error
Hi Getting the following error during install at select SQL Server "Full-Text Indexing needs to be running on the specified SQL server." I've installed Index Service but cannot find any full-text indexing options anywhere? Help Matt Hi Matt, only three suggestions (haven't had this problem so far): - check the service "Microsoft Search" and start it and set type to automatic - in SQL Server Enterprise Manager go to folder "Supporting Services" (don't know the exact english name as I only have a german SQL Server here) and check the serv...

Need help with cursor
Hello, I have a small GUI monitor application that doesn't have any controls on it. Basically its a frame with a title, menu bar, and client area. I dynamically size the frame for the contents of the client. The client window contains some status information about a service. I've noticed that the cursor gets changed to inappropriate values when the cursor is moved into the client area. For instance, moving the cursor in from the side may leave the cursor looking like the sideways-resize cursor. My application is written in MS VC++ v6.0. What events do I need to hook in...

!!!!!!!!!!!!! HELP THE CHILDRENS !!!!!!!!!!!!!!!!!!!!!!!!!!4
http://free.x3.hu/charity ...

Listbox Help (remove selected item)
This should be easy but I can not get it to work. I have a simple listbox with Row Source Type property of value/list. It is not mult select. I am using .additem to load the listbox and am not using the itemdata property. I want to be able to select an item in the listbox, click a button on the form to get the value in column 1 of the selected item, then remove the selected item from the list box. Thanks in advance for any help "sk" <sk@discussions.microsoft.com> wrote in message news:1BB03B01-F56E-4CDE-83DF-BF6550AD8593@microsoft.com... > This should b...

Installation CRM 1.2 on SBS2003 SP1 fails, please help
At the end of my installation I get an error windows with; Setup was unable to install Microsoft CRM Server Setup was unable to provision your organisation Setup was unable to create user settings. Access is denied. (80070005) Active Directory is in native mode I am installing as the administrator (domain admin) security account for services in Local System account Local System account and computer are added to the Pre Windows 2000 comp group Thanks in advance for any support Marc Did you enter your organization name precisely the same way it appears on your CRM 1.2 licenses? -- Matt ...