Replace every non empty cell

Hi,

I want to replace every cell with is not empty in a column with th
word "DEMO". Can someone help me?

Thanks,
Serg

--
serg
-----------------------------------------------------------------------
serge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=27555

0
11/5/2004 10:15:05 AM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
419 Views

Similar Articles

[PageSpeed] 40

To fill the blank cells in the used range of a column:

Select the column
Choose Edit>Go to
Click the Special button
Select Blanks, click OK

Type DEMO
Press Ctrl+Enter


serge wrote:
> I want to replace every cell with is not empty in a column with the
> word "DEMO". Can someone help me?

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/5/2004 12:16:30 PM
your data including blank cells is in C6 to C13

if you know vba put this in a module

Option Explicit
Public Sub test()
Dim cell As Range
For Each cell In Range("c6:c13")
If cell <> "" Then cell = "demo"
Next
End Sub

and run this sub(keep the cursor within the code statements and hit function
key F5

========
serge <serge.1f8trc@excelforum-nospam.com> wrote in message
news:serge.1f8trc@excelforum-nospam.com...
>
> Hi,
>
> I want to replace every cell with is not empty in a column with the
> word "DEMO". Can someone help me?
>
> Thanks,
> Serge
>
>
> --
> serge
> ------------------------------------------------------------------------
> serge's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4164
> View this thread: http://www.excelforum.com/showthread.php?threadid=275559
>


0
R
11/5/2004 12:16:46 PM
Select the range of cells.  Then open the Replace dialog (Edit, Replace). 
Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With 
text.  Then click Replace All.

-- 
Jim Rech
Excel MVP
"serge" <serge.1f8trc@excelforum-nospam.com> wrote in message 
news:serge.1f8trc@excelforum-nospam.com...
|
| Hi,
|
| I want to replace every cell with is not empty in a column with the
| word "DEMO". Can someone help me?
|
| Thanks,
| Serge
|
|
| -- 
| serge
| ------------------------------------------------------------------------
| serge's Profile: 
http://www.excelforum.com/member.php?action=getinfo&userid=4164
| View this thread: http://www.excelforum.com/showthread.php?threadid=275559
| 


0
jrrech (1932)
11/5/2004 12:28:12 PM
Oooh...every NON empty cell. <g>

Jim Rech wrote:
> Select the range of cells.  Then open the Replace dialog (Edit, Replace). 
> Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With 
> text.  Then click Replace All.
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/5/2004 1:12:49 PM
As Charlie Rich sang:  Welcome to my world.

hehehe.

Debra Dalgleish wrote:
> 
> Oooh...every NON empty cell. <g>
> 
> Jim Rech wrote:
> > Select the range of cells.  Then open the Replace dialog (Edit, Replace).
> > Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With
> > text.  Then click Replace All.
> >
> 
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/5/2004 9:20:10 PM
Thank you for your support. <g>

Dave Peterson wrote:
> As Charlie Rich sang:  Welcome to my world.
> 
> hehehe.
> 
> Debra Dalgleish wrote:
> 
>>Oooh...every NON empty cell. <g>
>>
>>Jim Rech wrote:
>>
>>>Select the range of cells.  Then open the Replace dialog (Edit, Replace).
>>>Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With
>>>text.  Then click Replace All.
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/5/2004 10:48:51 PM
Actually it was Jim Reeves who sang.

Gord

On Fri, 05 Nov 2004 15:20:10 -0600, Dave Peterson <ec35720@netscape.com>
wrote:

>As Charlie Rich sang:  Welcome to my world.
>
>hehehe.
>
>Debra Dalgleish wrote:
>> 
>> Oooh...every NON empty cell. <g>
>> 
>> Jim Rech wrote:
>> > Select the range of cells.  Then open the Replace dialog (Edit, Replace).
>> > Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With
>> > text.  Then click Replace All.
>> >
>> 
>> --
>> Debra Dalgleish
>> Excel FAQ, Tips & Book List
>> http://www.contextures.com/tiptech.html

0
Gord
11/5/2004 11:53:46 PM
hehehe

(Thanks Gord!)

Gord Dibben wrote:
> Actually it was Jim Reeves who sang.
> 
> Gord
> 
> On Fri, 05 Nov 2004 15:20:10 -0600, Dave Peterson <ec35720@netscape.com>
> wrote:
> 
> 
>>As Charlie Rich sang:  Welcome to my world.
>>
>>hehehe.
>>
>>Debra Dalgleish wrote:
>>
>>>Oooh...every NON empty cell. <g>
>>>
>>>Jim Rech wrote:
>>>
>>>>Select the range of cells.  Then open the Replace dialog (Edit, Replace).
>>>>Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With
>>>>text.  Then click Replace All.
>>>>
>>>
>>>--
>>>Debra Dalgleish
>>>Excel FAQ, Tips & Book List
>>>http://www.contextures.com/tiptech.html
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/6/2004 12:09:50 AM
Doh!

Charlie hummed it behind closed doors, though.


Debra Dalgleish wrote:
> 
> hehehe
> 
> (Thanks Gord!)
> 
> Gord Dibben wrote:
> > Actually it was Jim Reeves who sang.
> >
> > Gord
> >
> > On Fri, 05 Nov 2004 15:20:10 -0600, Dave Peterson <ec35720@netscape.com>
> > wrote:
> >
> >
> >>As Charlie Rich sang:  Welcome to my world.
> >>
> >>hehehe.
> >>
> >>Debra Dalgleish wrote:
> >>
> >>>Oooh...every NON empty cell. <g>
> >>>
> >>>Jim Rech wrote:
> >>>
> >>>>Select the range of cells.  Then open the Replace dialog (Edit, Replace).
> >>>>Enter a * (an asterisk) as the Find What text, and DEMO as the Replace With
> >>>>text.  Then click Replace All.
> >>>>
> >>>
> >>>--
> >>>Debra Dalgleish
> >>>Excel FAQ, Tips & Book List
> >>>http://www.contextures.com/tiptech.html
> >>
> >
> 
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/6/2004 12:48:06 AM
Reply:

Similar Artilces:

Format cell based on another cell's value
How do I change the background color of cell A4 based on the value in cell D7? i.e. If D7 = 3; A4 background should be green. Thanks in advance and Merry Christmas. Barney Barney, click in A4 and format, conditional formating, change cell valve is to formula is and put =$D$7=3, format to green, OK -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Barney" <postonbl@yahoo.com> wrote in message news:eF$gOvq6EHA.2180...

How do I change a cell background color in a formula ...
How do I change a cell background color in a formula => Excel 2003 eg. (IF(A1=1, change B1 background color to black ??? Use Conditional Formatting. 1. Select B1. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" on the drop-down list. 4. Enter: =A1=1 5. Press the Format button and format as desired. 6. Press OK twice. For more examples, see: http://www.xldynamic.com/source/xld.CF.html http://www.contextures.com/xlCondFormat01.html HTH Jason Atlanta, GA >-----Original Message----- > How do I change a cell background color in a formula => ...

Cells in Excel
How can I change letters assigned to cells to numbers? Thanks. Kelly, if you are talking about the column headers, tools options, general, check R1C1 ref. style -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Kelly" <anonymous@discussions.microsoft.com> wrote in message news:21c701c49a86$1d3dc8a0$a301280a@phx.gbl... > How can I change letters assigned to cel...

making a cell fixed number to a input number
I am doing a caclculation. Some of the time I want to do it with a percentage and sometimes with a dollar amount. If I punch in a dollar amount I want it to come up with the persentage. If I type in the percentage I want it to come up with the dollar amount. How can I make the formula stay in a cell even if I punch something in. fwday12@msn.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ And how do you tell .20 (%) from .20 (cents) "fwday" <fwd...

Conditional Truncation of a Cell
I have a list of 500 email addresses in excel. I am trying to figure out how to separate based on condition for example. If I have my.name@website.com, and I want to separate into a separate column all of the website addresses associated with the email address, how would I accomplish that? Likewise separating the name part for those prefixes containing a . or _ between the first and last name. Thx. Sounds like "Data - Text To Columns" is what you want. Look it up in the Help file. -- HTH, RD ============================================== Please keep all correspondence ...

Replace part of link address in a cell with a value(date) from ano
In MS Excel 2002: I have about 600 workbooks with 5 worksheets in each. To gather specific information into one spread sheet I want to pick one cell value from each(Sheet1, Sheet2, Sheet3, etc.) and paste into a column for each sheet, one row for each work book. Each work book's name include a date, so the link address to the cell can look like: =[Test040901.xls]Sheet1!$B$5 The date and the sheet number are the only differenses in the link addresses. Instead of manually going into each linkaddress to change the date, is there a way of 'copying' in a date from another cell to ...

Counting occurance of empty cells
Hi folks, I am trying to set up a formula which will count the quantity of empty cells in Column K if there is something in the corresponding cell in Column J These are what I have tried so far with no look =IF(ISBLANK(J157:J2643)," ",COUNTA(H157:H2643)) and =SUMPRODUCT(--(J157:J2643=">1"),--(H157:H2643="")) Thanks in advance for any help you can give Dave Perfect thanks, worked a dream "Tom Hutchins" wrote: > Try > =SUMPRODUCT(--(LEN(J157:J2643)>0),--(LEN(K157:K2643)=0)) > > I used column K per you...

Copying & Dragging Cells & Keeping Rows On all Screens
I am using Excel 2003 Sp 3 and Windows XP. I have a column Named Year Revised in which I place the year of revision for the item described on the row. This column has a format of General. The data in this column will either be blank or contain a year as yyyy. Many times when entering data on a new row I select the cells in the previous that have the same values and drag them down to insert in my row. My problem is that the data in the year revised cell will be incremented by 1. How do I prevent this? A second question concerns header rows. How do I fix row 2, which has my column ...

Measuring cell width and height for printed paper
The width of cells is displayed in characters and pixels. The height of cells is displayed in points and pixels. How do I know how many inches or centimeters a certain number of pixels will be on printed paper? Hi For one square inch. Try 12.45 (144 Pixels) for the width and 72 (144 Pixels) for the height. On my Dell printer its exactly one inch. HTH John "Pekka Numminen" <p.numminen@suomi24.fi> wrote in message news:aa2c2e4b-1ab2-4aab-b4c8-80a4f30aafcf@q16g2000yqq.googlegroups.com... > The width of cells is displayed in characters and pixels. The height >...

Replace
Hi Using Access 2002, I have a unboard text box on a contacts form for the contacts department. For uniformity I want to ensure the user always enter data, for example, in the form 'Health and Safety' instead of 'Health & Safety' i.e. replacing '&' with 'and'. I would appreciate if someone can advise if this is possible and, if so, an example of code. TIA Tom On Fri, 5 Oct 2007 17:24:58 +0100, Tom wrote: > Hi > > Using Access 2002, I have a unboard text box on a contacts form for the > contacts department. For uniformity I want...

hOW DO i CHANGE COLUMN WIDTH FOR ONLY A SELECT RANGE OF CELLS?
You can only change the entire column's width. A column cannot have two widths, tho you may be able to emulate it using merged cells. ************ Anne Troy www.OfficeArticles.com "RCONLON_291" <RCONLON_291@discussions.microsoft.com> wrote in message news:88E35FF3-F45D-44BB-A308-A16ADDB79876@microsoft.com... > activesheet.columns("D:G").columnwidth = 3 "Anne Troy" <ng@officearticles.com> wrote in message news:elivFHTuFHA.664@tk2msftngp13.phx.gbl... > You can only change the entire column's width. A column cannot have two > ...

How to reference cell that changes locations.
Here is a portion of my table: Shane $44,476.28 90.9 $67,103.19 49.2 $105,943.68 94.9 $59,601.69 92.7 $63,188.49 87.4 $81,969.00 91.6 $74,373.28 107.8 $83,153.10 91.3 $50,764.05 62.6 $62,418.70 82.8 $61,355.07 74.4 $69,577.73 87.4 Total $923,083.30 1128.8 Brantley $71,818.41 83.3 $130,694.34 126.9 $116,102.29 128.2 $97,046.07 112.6 $109,119.82 78.4 $105,529.95 111.7 $74,497.33 57.1 $77,313.42 78.3 $122,943.03 94.8 $76,781.16 80.1 $66,026.26 83.5 $63,825.67 58.4 $114,546.41 106.8 $106,373.49 102.9 Total $1,332,617....

Highlight cells greater than zero
I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie I would prefer to use conditional formatting instead of code. Look up Conditional Formatting in the help section it should help you. If you must use code you can use this: Sub HighlightCells() Dim rng As Range For Each rng In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) If rng.Value > 0 Then rng.Interior.Color = vbYellow ...

cells full of #####
I sent an Excel-2k spreadsheet to a friend and when she opens it up a column of cells with calculated values has #name? in it. The cells contain vlookup functions. I looked in the help for possible solutions but none of them solved this. I remember fixing this once a while ago and I just can't remember what it was - some obscure setting maybe? Help! Joe Post a formula that returns an error -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Joe Schmoe" <Joe@schmoe.com> wrote in message new...

Can't set cell.formula in Excel XP but Excel 2000 is OK
Hi! I have created a VB 6.0 app that read/writes to Excel Templates via OLE using late binding. The main problem is that it seems impossible to write a formula to a cell in sheet in Excel XP, but it works OK for one formula (or reference) in Excel 2000. The VB code looks like below, '--- xlWB.Sheets("Report").Activate xlWB.Sheets("Report").Range("B5").Select xlWB.Sheets("Report").Range("B5").Formula = _ IIf(Left$(sFormula, 1) = "=", sFormula, "=" & sFormula) '--- The formula in the example is like, sFormula ...

Color Coding cell based on date (over a large range)
I have a "calendar" that I want cells color coded based on a date entered in a reference cell. I've tried it a couple of ways using conditional formatting (which I assume is the correct way to do it), but can't figure out exactly how to accomplish it. I.e., given the following type of spreadsheet entry... A B C D E F G H I J K 1 JANUARY 2 1 2 3 4 5 6 7 8 9 ... 3 _ _ _ _ _ _ _ _ _ (C3 would be Jan 1, D3 would be Jan 2, etc) If a reference cell has Jan 1 in it, then C3 would be highlighte...

Totalling non adjacent cells in a spreadsheet
Hi all I am running WXP with Excel 2003 edition. I wish to know if, and how to set up a running total for various predefined categories in a spreadsheet. I have set up my monthly expenditure with debit & credit columns, totals are fine. I list all for say, August, enter Septembers beneath with a C/F balance, each expenditure is given a category name i.e. Utilities, Housekeeping etc. Can I then select all the entries entered under say, Housekeeping, and place them into a cell as a running total, (doesn't matter where i put these results on the spreadsheet) other than manually en...

Merged Cell Character Limitation?
Is there a limitation to the number of characters that can be entered into a merged cell? I have a document which has a merged cell consisting of 12 rows and 9 coulmns (for text entry). I set the cell to word wrap. However the word wrapping ceases after roughly 1500 characters, and the text is no longer visible to the user in the cell (even though there appears to be room). If this is a limitation, is there a known work around? Hi there's a maximum of 1024 characters per cell. Though you can increase this by manually adding line breaks with ALT+ENTER -- Regards Frank Kabel Fra...

Vary variables in a formula via reference to another cell
I have a formula =SUMPRODUCT(--(b1:b10=a1),c1:c10) for a report I run January. In Feb, I want to change c1.c10 to d1.d10 and in March to e1.e10 etc. Ideal I'd like to name range c1.c10 "Jan" and d1.d10 "feb" etc and then enter the month range name in cell x1 (eg: Jan) and have the formula pick it up eg: =SUMPRODUCT(--(b1:b10=a1),+X1). But I can't get it to work! Any ideas....? TVM. -- Peter London, UK mask the cell x1 with indirect function as follows: =SUMPRODUCT(--(b1:b10=a1),(indirect(X1))) "Peter" <Peter@discussions.microsoft.com> wrote ...

How do I make a cell required to edit in Excell?
I created a template Accounting Sheet for my office to use but they constantly forget to fill in the date. How can I make that field required to edit before saving? You could use conditional formatting to fill the other fields with black, unless the date cell has a value in it. For example, if the date goes in cell D3: Select the other cells where data should be entered Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the Formula box, type: =$D$3="" Click the Format button On the Pattern tab, select the black colour Click OK, click OK Be...

Deserialize empty XML element (VB.NET)
I'm having some trouble figuring out how to deserialize an empty XML element in VB.NET. Let's say I'm trying to deserialize XML such as: <Order> <OrderRefNumber/> <Customer>Bob</Customer> </Order> I would then have a simple entity object to represent the XML data. That object would have a property such as: '---------------- begin code ----------- <System.Xml.Serialization.XmlElementAttribute(Form:=System.Xml.Schema.XmlSchemaForm.Unqualified)> _ Public Property OrderRefNumber() As Long Get Return Me.r...

Random Number Non Repeating ...
I have the numbers 1 - 55 that I would like to display in a column randomly without repeating. The numbers should be whole numbers only. Can you help me? I tried following some of the other posts, but I couldn't get it. Thanks. Enter the numbers 1-55 in a column, and in an adjacent collumn enter =RAND() and fill down. Now sort both columns by the RAND column and you'll have the 1-55 shuffled. Bob Umlas Excel MVP "a_ryan1972" <a_ryan1972@discussions.microsoft.com> wrote in message news:DBC6E0F7-E136-4CEC-991E-25C5302732DD@microsoft.com... > I have the numbers ...

separate letters from cell
Hi, can someone help me with the following: In 400 cells I have a name + a code with three letters, for example: Mister Blackwell RRT. I would like 'Mister Blackwell' in one cell, and 'RRT' in another cell. Is it possible to separate the three letter code from the name? Thanks a lot in advance. Marieke Hi, to get the name =TRIM(LEFT(A1,LEN(A1)-3)) To get the 3 letter code =RIGHT(A1,3) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the q...

CELL FORMAT #6
hello, Can somebody help me in solvin the follwoing problem... I have a excel sheet which has 10000 customer's information but not formatted in the sense name,age,employment together in one cell. The datas i have, dont have any commas or spaces and more over all the fields like name,age are together and broken into 2 or 3 different rows. i wanted to seperate it and put it in a table format. the sample data is as follows. name ddddd last pd date 12/10/2003 age 23 employee ffffff employer fghfghfgh. the result what iam expecting is as follows. name lastpddate age employee em...

The selected area grows when I click a cell or press keys to move.
The F8 function key seems ineffective in turning of the extended-selection function. I'm using Excel 2000 SP3 I should by grateful if someone would advise me how to terminate this feature. You could try downloading and installing a new version of the mouse driver, if one is available from the manufacturer's web site. Or, try a new mouse and driver on the machine. David McRitchie also has some suggestions on his web site: http://www.mvps.org/dmcritchie/excel/ghosting.txt yendorexcel wrote: > The F8 function key seems ineffective in turning of the extended-selection > f...