i have colums of data containing random figures containing letters and numbers.
I want to completely remove the letters from the colums, leaving me with just a numerical figure. Is there a way to do this? There is no set pattern to work with as there could be 1 cell with 2 numbers and 6 letters, and the next cell may have 6 numbers and 1 letter.
EG: COLUMN A
aaa567783a
3782dc
23388fjjas2
22dd
I want this to simply be
EG: COLUMN A
567783
3782
233882
22
I have to convert an old dos program file to excel. I already have the list in excel but the text is in strange symbols. I know which symbol represents which letter. As have many files to convert each time I need a makro which would save me lot of time. I have two worksheets: "1" and "2". I need a makro which replaces symbols in sheet "1" Column A through key list in sheet "2".
I would like to easily replace every character in column A with a blank, with the exception of - and a number. I am trying to get rid of all letters,:, / and other miscellaneous characters without having to delete them one character at a time.
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and Find all occurences of With or WITH and replace with with. Find all occurences of Or or OR and replace with or
I need to find a formula that will find letters in a referance, for example i have referances like - MNE DJM & ZZPAR i need to find a formula that will find me the ZZPAR looking for "ZZ" i then want this to tell me what tpYe of referance number it is and put this into column Z.
Normal referance number like DJM and MNE i want this to show as "BROMLEY"
aND ZZPAR as "Chester"
I have tried something like the following but this is not working
In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, cell A4 is PM-B00, etc. The first two positions are always alphabetic. I want to sum all the numbers in column B whose adjacent column A text starts with "PM". I tried =IF(match("PM*",A2:A100,0),b2,"") but just get "NA"
Log sheet Col a = Dates Col d = Route ( Letters and numbers) Col F = Times (1.2-2.3- examples) Log sheet has 1,550 rows +
How I have been finding total for the Last 12 months is the formula below
=SUMIF(LOG!A:A,">="&TODAY()-365,LOG!F:F)
Now what I would like to try and do is the same BUT. using COL D (on LOG sheet) to find out when I went to for example "JTF" be advised that COL D is a route, so there are many results in each cell in col d.. So examples below
COL F On Log sheet OERT-JPF-JMF-ASG1-JTF OERT-JTF-JTF-ADC17-ADC17-ARAB3-ARAB3-DHAMC-JSK OERT-JTF-JTF-ASG1-R655-JPF-JMF-LCL-JTF OERT-JTF-JTF-R655-DHAMC-HAW-DHMAC OERT-JTF-JTF-JTF-HI4-HI4-R655-ADC38-RS55-ES76-JPF-JMF-JPF-JMF OERT-JTF-JTF-HI4-R655-ADC38-ES76-PMT-FLIGHT TOUR VIPS
The content of Cell A1 looks like this attccggttaattcccccaaaattt (only a,t,g,c -nucleotides). I want to know the max times C occurs in this cell and the position from the start. like that a, t, g.
here the answer is 5 times and distance is 13 from start.
I am looking for a formula that will return the lowest value from a five cell range using letters instead of numbers. If the 5 cell range is empty the cell will remain blank. Not all the 5 cells may be used - it could be anywhere from 1 to all cells.The weightings of the letters in terms of their numerical value are as follows:
F=0 P=1 M=2 D=3
Examples of desired results:
From A1 to A5 the following letters are inputted: P M M D P. Result in A6 = P as P is the lowest numerically in the above list. B1 to B3 = D D M. Result in B4 = M. C1 = F. Result in C2 = F. All cells blank from D1 to D5 = cell in D6 remains blank.
Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.
So in a 5 cell range the values could be:
blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code: If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt 34.2 36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
I was wondering if it is possible to do this unique find and replace that is explained below.
I have many columns with data from our database. I have one column that has my html layout in it. I want to find within the html text data and replace it with data from another column for that row.
Example: We have [[manufacturer]] in the html area that we want to replace with data from our column called Manufacturer. So, it will replace the text in the html named [[Manufacturer]] with that rows Manufacturer data we have.
I have around 10 names which I want to replace with their code names. For Example, "Alan Richard" needs to be replaced with AR001. Problem is I don't know cell range and this name might appear more than once in the data.
I am trying to find and replace #N/A from a formula (link to another sheet) with nothing in the cell, using VBA.
Using this code it will do the first "find" Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True, SearchFormat:=False).Activate ActiveCell.ClearContents Trying this Cells.Replace What:="#N/A", Replacement:=" ", LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _ ReplaceFormat:=False LookIn:= is highlighted and I get a "named argument not found" Don't understand that because LookIn:=xlValues works in the first code.
I did try and place After:=ActiveCell,before this, but that does not work. I Get the same error.
I have more then one cell with the #N/A which I would like to get rid of. I want this to work only on this one sheet. The other sheet that the formula is pulling from does have #N/A, and that is okay, I need it there.
I have a list of sales associates that are assigned numbers. For example, John Doe is 1022. The data I have to work with only shows his number 1022 but for my reports management wants the name John Doe. I have about 60 sales associates and have a VB code that 'finds and replaces'. The code is huge. It has slowly grown out of hand, with more sales associates added daily. I also have 22 sheets in one workbook with specific columns to search for the numbers to replace with the names.
This is what I would like to do. Have a sheet named associates, as more associates are added I would just have to add their name and number. Have two columns with headers column A - Associates column B Associate #. Name ranges for the two columns and write a code that says look in the sheet associate, at named range 'numbertoname' and if the current sheet 'total sales' I am in has the number 1022 in column C replace it with the name John Doe. It seems like it should be easy but.......I have tried and tried today.
I am facing a strange problem with Find and Replace function of Excel. I converted a PDF document into a Word document. I copied the tables in the Word document to an Excel document. As the data had many ‘*’ characters, which I did not want, I used Find and Replace function to get rid of them. However, to my surprise, two things were happening.
1.When I tried to replace ‘*’ in a cell with ‘ Construction revenue ********************** ’, it was deleting all the contents of the cell. 2.When I tried to find all the cells with ‘*’ , it was selecting cells even without ‘*’
I tried various things like changing the format, pasting only values..etc, but nothing worked. Finally I had to use the Replace function of VBA to do the task. For further analysis I am attaching my Excel file with that data.
In the attached file I want to do a Find/Replace for 599 --> 599.0 and 428 --> 428.0. When I do a F/R for each it tells me it has made 3 and 5 replacements respectively but the data is not changed?
HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search?
I'm using Excel 2007 and trying to replace about 2000 commas with full stops. I want to use find and replace but keep getting a message saying that Excel cannot find the data I'm searching for.
Excel help suggests I haven't clicked on "find" before clicking "replace" but I did. I've tried highlighting the column in which I want to make the replacements, but same message.
Also tried highlighting nothing, same message.
Tried copying and pasting the whole thing into a new file, but same message.
The Formula bar shows: ="01/01/2009" The cell presents: 01/01/2009
I need to remove the equation sign as well the two inverted-commas.
I am familiar with the "Text to Column" feature, the use of SUBSTITUTE Function, a short macro and also the Find&Replace is also a good idea but, as far as I understand, it must be run Twice(!)
I am looking for a way to use Find&Replace in "One! shot" - meaning, to put the TWO different(!) characters [ the '=' and the " ] in the 'find what' window, leaving the 'replace with' window empty.
I know that something similar to that can be done in a "Word" document - but can it be accomplished in Excel?
