Replace

I want to replace in a column every thing that is not = 
to "Header" with detail
0
anonymous (74721)
4/22/2004 2:03:46 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
447 Views

Similar Articles

[PageSpeed] 52

Hi
not really sure what you're trying to achieve. Could you 
give an example.e.g what is your 'detail'

>-----Original Message-----
>I want to replace in a column every thing that is not = 
>to "Header" with detail
>.
>
0
frank.kabel (11126)
4/22/2004 2:34:55 PM
If I have in Column A diffrent data ex:
A1:JFK001230
A2:FRA001630
A3:Header
I want a macro to replace all the text in column A that 
is not equal to "Header" with the word "Detail.
hope is is more clear
>-----Original Message-----
>Hi
>not really sure what you're trying to achieve. Could you 
>give an example.e.g what is your 'detail'
>
>>-----Original Message-----
>>I want to replace in a column every thing that is not = 
>>to "Header" with detail
>>.
>>
>.
>
0
anonymous (74721)
4/22/2004 2:43:26 PM
If I have in Column A diffrent data ex:
A1:JFK001230
A2:FRA001630
A3:Header
I want a macro to replace all the text in column A that 
is not equal to "Header" with the word "Detail.
hope is is more clear
>-----Original Message-----
>Hi
>not really sure what you're trying to achieve. Could you 
>give an example.e.g what is your 'detail'
>
>>-----Original Message-----
>>I want to replace in a column every thing that is not = 
>>to "Header" with detail
>>.
>>
>.
>
0
anonymous (74721)
4/22/2004 2:43:28 PM
Hi 
try the following macro:

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
        with Cells(RowNdx, "A")
            if .value = "Header" then
                .value = "Detail"
            End If
        end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

-- 
Regards
Frank Kabel
Frankfurt, Germany


anonymous@discussions.microsoft.com wrote:
> If I have in Column A diffrent data ex:
> A1:JFK001230
> A2:FRA001630
> A3:Header
> I want a macro to replace all the text in column A that
> is not equal to "Header" with the word "Detail.
> hope is is more clear
>> -----Original Message-----
>> Hi
>> not really sure what you're trying to achieve. Could you
>> give an example.e.g what is your 'detail'
>> 
>>> -----Original Message-----
>>> I want to replace in a column every thing that is not =
>>> to "Header" with detail
>>> .
>>> 
>> .
0
frank.kabel (11126)
4/22/2004 4:56:17 PM
If you like to avoid loops, and your worksheet can handle it, perhaps
another option.

Sub Demo()
    ActiveSheet.UsedRange
    With Columns("A:A")
        .Replace What:="Header", Replacement:="=#N/A", LookAt:=xlWhole,
MatchCase:=False
        .SpecialCells(xlCellTypeConstants, xlTextValues).Value = "Detail"
        .SpecialCells(xlCellTypeFormulas, xlErrors).Value = "Header"
    End With
    ActiveSheet.UsedRange
End Sub


-- 
Dana DeLouis
Using Windows XP  &  Office XP
= = = = = = = = = = = = = = = = =


<anonymous@discussions.microsoft.com> wrote in message
news:2c6601c42878$2ca601c0$a501280a@phx.gbl...
> If I have in Column A diffrent data ex:
> A1:JFK001230
> A2:FRA001630
> A3:Header
> I want a macro to replace all the text in column A that
> is not equal to "Header" with the word "Detail.
> hope is is more clear
> >-----Original Message-----
> >Hi
> >not really sure what you're trying to achieve. Could you
> >give an example.e.g what is your 'detail'
> >
> >>-----Original Message-----
> >>I want to replace in a column every thing that is not =
> >>to "Header" with detail
> >>.
> >>
> >.
> >


0
delouis (422)
4/22/2004 5:38:19 PM
Reply:

Similar Artilces:

Find and Replace 04-13-10
Good day all, I am trying to change this number 01-465-9273 to show up as 014659273. It is easy enough to make the (-) go away but it takes the zeros at the beginning of the number away as well which defeats the purpose Thanks -- Pierre A couple of alternatives: - format the cell as Text before removing the dashes - put an apostrophe before the 0 before removing the dashes - remove the dashes in another cell, using SUBSTITUTE: =SUBSTITUTE(A1,"-","") - give the cell a custom number format like 000000000 Hope this helps, Hutch "Pierre" ...

how to replace a date with new date
hi, how to replace an existing date with a particular dat -- Message posted from http://www.ExcelForum.com Hi you may be a bit more specific :-) can you give an example of what you're trying to achieve? -- Regards Frank Kabel Frankfurt, Germany > hi, > how to replace an existing date with a particular date > > > --- > Message posted from http://www.ExcelForum.com/ ...

Can I automatically replace data when importing a text file?
I am importing data that has hundreds of numbers like 60003838, 60010813, 60003928, 60003895. I would like to know if there is a way to format the data to change those numbers automaticaly to another value (names) every time I import the data, or is there a way for me to replace all those values automatically? If you have a list that has names behind these numbers you can use a vertical lookup function ( VLOOKUP ). Use Insert - Function, and search for VLOOKUP function and follow the instructions. Hope this helps. "Jake" wrote: > I am importing data that has hund...

Find and Replace
Having prduced a list of values from using the & concatenate method, I want to end up with a list of only those which have a positive (over 0) number. So I have: (155), (111), (51), (0), (0), (0), (0), (0), (0), (0) for example. What I have tried is to do a find and replace finding ", (0)" and replacing it with nothing. Works great, until I look at the: (66), (0), (0), (0), (0), (0), (0), (0), (0), (0) values. Doing the replace I end up with: -66 Or with: (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) I end up with: 0 (OK this one is easy to delete simply fi...

Replace Worksheetfunction.ln code
Morning, I want to replace the code "worksheetfunction.ln" with "math.log" in my code. Is this the same calculation? If not what piece of code should I be using. Thank you very much for your help, Michelle The math constand "e" is approximately 2.3. To get the exact value you use the ln(1). Ln and log are equivalent functions that give different results because the base is different. Ln the base is the constand "e", while log base is usually 10 but can be others bases. There are 3 functions in excel LN(Number), LOG(Number,Bas...

Replacing multiple returns with one
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi there--I have a very long document that is a listing of names/phones/addresses. After each entry is 4 returns. I'd like to strip these out and have only one return after each entry. <br><br>Is there a way to do this? There are thousands of entries, so it would be a huge time-saver. <br><br>Thanks--d Use Edit> Replace, enter ^p^p^p in the Find What field & don't put anything in the Replace With field, then click Replace All. HTH |:>) Bob Jones [MVP] Office:Mac ...

How do I replace a negative number at the end of a formula with a.
I have a formula in excel that calculates overtime hours for my employees. Hours are deducted from overtime for personal time and comp time. If the personal and comp time hours are more than the overtime hours it gives me a negative number and then because it is a negative number it adds hours to the regular hours pay. I need to have the formula treat a negative number like a zero or equal a zero and not a negative amount. Here is the formula I use. =IF(G45<212,0,G45-212)*1-(E67+C67+E51) Thanks for any help you can give. use ABS abs(negativenumber) -- Don Guillett SalesAid So...

Can I replace the attachment file when double chick attachment?
I want to replace the attachment when double chick the attachment. I use outlook form to do it. Beause attachment read in outlook is not a cancelable event, I can't do it directly. I found outlook always store attachments file in directory: "%USERPROFILE%\Local Settings\Temporary Internet Files\OLK*". So if I want to replace the attachment when user double chick the attachment, I can repalce the attachment file(will display) in that directory. I have some test, outlook allow me do that. But I meet two question: First: the directory is different with each user, I always start wi...

How do I do a replace for a number of records?
I am importing a file which will contain about 20 records (20 fields per row) into an access database table. I want to be able to automate doing a REPLACE across all the records I have imported. (because. in order to make the import work, in the Excel csv file I replaced all commas with the word 'comma'. I now want to replace the word 'comma' with actual ','). Do I make sense? any help very gratefully received. thanks Nicola Access 2007 Continue this SQL string for all 20 fields using your table and field names. UPDATE 1st_Table SET [1st_Table].Field1 = Replace(...

Re: Ordering a replacement disc.
I need a replacement disc for Publisher 2000 (disc 1)which cracked and is unusable. Does anyone know who to contact to order a replacement? "Jeanne Jelinek" wrote: > I need a replacement disc for Publisher 2000 (disc 1)which > cracked and is unusable. Does anyone know who to contact > to order a replacement? ===================================== To obtain replacement CD-ROMs or manuals, contact the Microsoft Replacement and Supplemental Parts Department at (800) 360-7561. When you call for a replacement CD, you may be asked to fax a copy of the store receipt to the Mic...

Replacing Character in Cell
Hi Everyone, I am trying to replace certain characters in a cell using Ctrl + F -> Replace but I think I am doing something wrong. Can anyone help? 1. I have Number(1 to 13) & Alaphabet combined code looking like thingy from A1:AM65536. Yes there are alot of them. e.g. 1D2D4C13H12C etc 2. I am just trying to replace 13 with "K", 12 with "Q" 11 with "J" and 1 with "A". 3. How do I replace those? thank you If you do "Edit & Replace" 4 times, starting with 13 first, then working down, I don't see any problem. That is, unless...

Why is my attachment being replaced with Winmail.Dat?
I have been having some troubles with attachments lately. I have a file either as a .DOC word document or zipped to .ZIP. I send that file to a group of persons. They are using Lotus Notes. Some of them receive the file without trouble. Some do not receive the file. It is replaced with a file called winmail.dat that does not contain the original info. I have tried sending the same file though my ISP's webmail and they all receive it fine. If I send the file from outlook using Plain Text instead of Rich Text or HTML they all receive it fine. What's the scoop? Anyone? James ...

Replace no init backup with init backup?
I have created some databases no init from SQL Server 2005 maintenance plan. I want to do Init backups and override the backups I made in the past with no init. If I do through SQL Agent code, will this take care of the problem or some other way? SQL (texassqldba@gmail.com) writes: > I have created some databases no init from SQL Server 2005 maintenance > plan. I want to do Init backups and override the backups I made in the > past with no init. If I do through SQL Agent code, will this take care > of the problem or some other way? I'm not sure what your question is...

Unicode and Search and Replace
I have Excel 2003, and in a column I have various unicode Greek letters in the Gentium font (such as ό) that I would like to search and replace into numbers (ό would become 15) so I can sort the sheet (the search and replace doesn't work for certain unicode Greek letters). Other than paying a lot for an expensive add-on or other software product, is there any way I can do this in Excel 2003? Please explain in simple English. Thanks. Excel's usual Find / Replace can handle the job without addins. 1. put a single instance of any unicode character in an un-used cell 2. copy the...

How to search and replace multiple tabs
I am trying to create a macro to search and replace one tab with two in a TOC. Problem is I currently have three tabs set in the paragraph and the one that I want to replace is the second tab. When I run the search and replace it changes the first tab to two. Any ideas on how to get around this problem. Ex (current): 1.(tab)text(leader tab)digit Ex (desired): 1.(tab)text(leader tab)(tab)digit Submitted via EggHeadCafe - Software Developer Portal of Choice ASP.NET: Hamburger, Yes! AJAX, "NOT!", Redux! http://www.eggheadcafe.com/tutorials/aspnet/bf10c5a6-0c8e-4ba9-934d...

Rolling up and replacing standard costs
We are getting very, very mixed results when trying to make changes to a component of manufactured item, and rolling those changes up to the parent(s). that use it. It appears to matter what portion of the BOM you are selecting on the standard cost changes screen. The help files dont' document what needs to happen. Is there any documentation that explains how to do this in a more detailed fashion? -- Richard G. Ward Controller Storm Manufacturing Group, Inc. ...

Find and replace according to conversion table
Hi, I have a question about automating find and replace for text data. I havetbe replacements I want to take place. Instances of A1 should be replaced with the value in B1, A2 with B2, and so on. There are several thousand entries. How can I use this information to do a large-scale search and replace conversion on data listed in a third column? The third column contains text characters. So, A1 = A, B1 = D A2 = B, B2 = E A3 = C, B3 = F and I want this information to convert C1 = ABC to D1 = DEF Can it be done? -- efrancis --------------------------------------------------------...

Replace Cell Address in A Long Formula
From time to time, I need to do some change in some very long formula. For example: =TRIM(IF(ISERROR(FIND(" ",B46,FIND(" ",B46,1)+2)),MID(B46,FIND(" ",B46,1)+1,LEN(B46)),MID(B46,FIND(" ",B46,FIND(" ",B46)+2),LEN(B46)))) I want to replace all the "B46" with "F18". Is there a easier way? Thanks. One quick and dirty way is to Edit>Replace B46 with F18. HTH Anders Silven "plumstone" <plumstone@discussions.microsoft.com> skrev i meddelandet news:29A2448B-1055-4847-9779-EA0FDDA5C05D@microsoft.com... > From...

Replace Data In A Table
I'm using sql 2000 and Access 2003. I have a table contains all the payroll data and a table that contains current payroll data. Each paydate I need to replace the data in the current payroll table with the current payroll data. In Access I could run a Make Table query that would do this. How can I do this with sql and Access? Thanks for the help, Paul Continue to use Access if your SQL programming skills are at issue. Use an Append query to add the data to the end of the existing table. "pjscott" wrote: > I'm using sql 2000 and Access 2003. > > I have...

Newsreader links don't work after replacing antivirus/firewall software
I use Outlook2002 and it uses OE6 as its news reader. Since I installed McAfee Internet Security Suite 2006 8.0, nothing happens when I click a link on a news post. Links work fine when they are within an email. Al Clark On Fri, 12 May 2006 00:47:30 -0700, Al Clark wrote... > >I use Outlook2002 and it uses OE6 as its news reader. Since I installed >McAfee Internet Security Suite 2006 8.0, nothing happens when I click a link >on a news post. Links work fine when they are within an email. > >Al Clark > > I use a different brand but may want to check the port# r...

Find and replace text thats not wdAlignParagraphCenter alignment
Im trying to make a macro that set alignment on all text to wdAlignParagraphJustify, except text that is wdAlignParagraphCenter. Right now i have this: With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphRight With .Replacement .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphJustify End With .Execute Replace:=wdReplaceAll End With With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphLeft ...

replacing unsigned char * with CString
Okay, for some reason this one has me stumped. I am trying to replace all of my char * (and unsigned char *) with CString's so to (hopefully) clean up the code and make it much easier to track down, fix, and avoid errors (such as not releasing memory, or not allocating enough memory, etc.). My problem has arising when I try to read a lot of (essentially) random characters form a text file using the CFile::Read operator. I am going to show both version of my code below (totally stripped down), hopefully someone can tell me how to use the CString properly in this case (or if I canno...

Replacing a comma ( ,) with a new row
Hellol everyone, I have being working ont this spread sheet for a long time because my work requires that I incert a number in a single cell in a new row. But some of the cells containt hundreds of numbers separated with a comma and this makes my life hell. I would like to know if there is any way I can automatically replace the comma with a new row. If anybody can help me out please email me at spiderweb14@gmail.com. Thank you very much. -- spiderweb ------------------------------------------------------------------------ spiderweb's Profile: http://www.excelforum.com/member.php?...

SBS 2003 Backup
Helo, again a quicki one: I have scheduled backup set to be written on external HDD, with replace option so I have Small Business Server Backup.bkf, Small Business Server Backup (2).bkf... and so on until external hard is filled up, and then it shoul replace oldest .bkf... I exchanged external hard (they are held in safe) with other, cleared it, and now I have Small Business Server Backup.bkf growing on daily basis, (some 15 GB per day), so it is in append mode????? is there a optionswitch to add to command line to set it again to replace mode? Are you using the SBS 2003 Ba...

How to replace a function with its result or resulting reference in a formula?
Hello How to replace a function with its result in a formula? For example, =INDEX(...)+INDEX(...) with =result_of_INDEX(a...)+result_of_INDEX(b...)=1025+1307. How to replace a function with its resulting reference in a formula? For example, =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11. I need to replace in 1000 cells automatically. "Dmitry Kopnichev" <kopn@bk.ruDelete> �������/�������� � �������� ���������: news:%23cF5PL9zFHA.916@TK2MSFTNGP10.phx.gbl... > Hello > How to replace a function with its result in a formula? For example, > =INDEX(...)+INDEX(...) with > =r...