I have numeric values in approx 1000 cell entries that I need to edit:
example cell C1 contains the value 00100300308W400
I'm trying to edit this entry to show 00/10-03-003-08W4/0.
I've tried using wildcards with the SUBSTITUTE function:
=SUBSTITUTE(C1,"***************","**/**-**-***-****/*")
Following code doesn't work
Dim Value1 as String
Value1 = Substitute(Cells(4, "A"), "A", "", 1)
correct syntax to assign the result of substitute function in the Variable?
In cells G2 to G57 I have the months from Jan-09 to Aug-13. In H3 to H57 I have the monthly data. In cell k2 I am trying to average all of the December data without individually clicking each cell (Ex: Dec-09, Dec-10, Dec-11, Dec-12). I was thinking of doing Averageif formula like =AVERAGEIF(G2:G57,"Dec*",H2:H57) but I get the dreaded #DIV error.
View 1 Replies View RelatedI am using COUNTIF functions with Defined Name lists to quickly determine if a certain number is on a list. As a backdrop, I am using Chemical Abstract Service (CAS) numbers and attempting to somewhat streamline chemical approval for a small company. CAS numbers are often in the format of XX-XX-X with varied amounts of numbers. One of the defined lists however does not have dashes.
Thus, I am using cell B1 to enter the CAS# once and then for each list having a column to itself with an associated worksheet with a defined name list. Most of the columns have the function =B1 with the conditional formatting of =COUNTIF(definedname,BX) and formatted to turn red if the chemical is on the list. This is working for all of the columns except for the list that needs the dashes removed. For instance, CAS 64-67-1 is put in B1 and cell B5 has the formula =SUBSTITUTE(B1,"-","") which brings the number to 64671 which matches the number in my defined name list. However, the cell will not turn red. What am I missing?
I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.
Value Real Value (after substitute function)
0,1831 0.1831
23,3333 23.3333
12,5199 12.5199
5,5000 5.5000
20,5999 20.5999
24,4671 24.4671
200,0000200.0000
2,5386 2.5386
0,4000 0.4000
1,5019 1.5019
how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.
In trying to create a formula that remove EVERY symbol from a cell (C6) and replacing it with a space.... the "SUBSTITUTE" function is telling me I have too many nested Substitutes.
This is the formula:
Code:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(C6,"/"," "),"%"," "),"!"," "),","," "),"*"," "),"-"," "),"("," "),")"," ")
It works thus far, but I would still like to add more symbols into the nest. Is there a way/formula to do this that replaces ANY symbol with a space?
is it possible to make a SUBSTITUTE finction non- case sensitive?
For example I want to replace all letters "e" and "E" in a cell.
Got the following formula:-
=LEFT(L5, FIND("(",L5)-1)
I need to add the SUBSTITUTE function to this but can't figure out where it goes if somebody could point me in the right direction please? My substitute formula is SUBSTITUTE(L5,"car","train").
I've in cell A1 an entry like this: 123*456*7890
=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890
=ISTEXT(A2) in cell 'A3' gives me: TRUE
BUT, =A3+1 gives me: 1234567891
Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?
I want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?
as you are limited to 7!!!
excel 2003!
=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))
Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example
A B
Book 345353hg dgdgsdgfd fff Book belongs to Jim
Sneaker fdg4 Sneaker is worn by Jan
Gum dfg s d e te4345Gum tastes great
Cake jklsjflsjfjikCake smells delicious
The pattern is that the A1 text appears in the B2 text. What I want is
Find *A1
Replace with blank
Find *A2
Replace with blank
and etc.
So that in the end, I get this:
B2
belongs to Jim
is worn by Jan
tastes great
smells delicious
I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.
I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.
However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.
It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.
Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.
My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.
I want to confine the results of a table to one single cell but also want to include “hard returns” within the formula otherwise when I go to print, it won’t fit on the one page.
Data table is as follows:
A1 = 10
B1 = 10
C1 = empty cell
D1 = empty cell
E1 = 30
F1 = 30
Formula below includes one “hard return” for each cell:
I'm using the sum if function but can't get the correct answer because the text that I'm searching for includes * which I assume is the wildcard symbol. I'm looking to sum on the text integrated projects* but it's returning a value which also includes integrated projects* VAT.
I don't want to have to change the text (e.g. by using find and replace). Is there any way excel can incluce just the exact text I need (including *)?
I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.
on one page I have a list of dates in one column and a list of numbers in another column. I want to look up January say in the dates column and sum the relevant numbers in the other column, (all the dates start with 01, i.e. the first day of the month), so I tried:
=SUMIF(Sheet2!A6:A117,"01/01/????",Sheet2!I6:I117)
If I put in the exact date, i.e. replace ???? with 2008, then it works fine, just returns the number for that one date, but with the wildcards, I always get zero.
I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.
I am attaching the spreadsheet,
I have a formula who examines if they are the same. The last number at first quote is not a problem (the 45). The problem occurs only and allways when the horse's name have an apostrophe " ' ". In first example apostrophe is different from the usual. If i manually delay it and replace it - type keyboards apostrophe everything works fine. Obviusly its a symbol. I want a small macro for replacing all these symbols at, lets say column B ,where these names are located. How can i use substitute at this case?
I understand that Microsoft left Filesearch out of VBA for Office 2007 because it was buggy. Sadly, however, I still need it. It would help if they placed a comment in VBA help that stated that it was discontinued. As it is, they make it look as if Filesearch is still available for use. OK, I'll stop ranting now.
After reading several earlier posts, apparently I have to use the Dir function. I learn visually and cannot figure out how to use the function from the VBA help file since there are no examples. Does anyone have an example of code using the Dir function to insert filenames in an array? Once I see the code, I'm sure I can adapt it to suit my needs.
I have a whole bunch of dates in a column that look like this:
5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.
*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.
Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?
Here's my ....
I'm doing a search simular to this
=VLOOKUP(D*,Sheet2!A*:B*,2,FALSE)
and I want it replaced with this
=VLOOKUP(D3,Sheet2!A1:B42,2,FALSE)
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 working with several columns of data that represent the types of employees we have at my organization. I'm trying to count the number of each type by status, as well as determine the salary payments to each type.
My problem is that we have titles like "PAA I" "PAA II" and "PAA III" and I've not been able to quite figure out the right formula to deal with this problem.
how to do a find and replace on the following
090226000/Jones/123 Main Street
All data has numerical 9 places first field
I need to eliminate the /Jones/123 Main Street
so that I just have 090226000
I thought * would work but it's not returning the
correct output
I have an excel file where the user has to enter his user name and password. But the password that is entered into the cell should appear as Wildcards(*) to everyone (Like our Windows login). Is there any way to do it.
View 1 Replies View RelatedI've got a column with client codes (col. B) and another one with material codes (col. C). Both columns mix numbers and text. First condition: If cells in "B" column equal a specific code ("DA2")
Second condition: AND If the cells in "C" column start with a certain pattern ("30") followed by some other pattern (four random digits, a dash, and two or three more random digits). THEN a new cell should say "T1", Else it should just copy another cell.
=SUMPRODUCT(--('Trial Balance'!A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.01.01"},0))),'Trial Balance'!G1:G100)
Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.
Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.
I'm trying to use wildcards in the Search formula and it isn't working the way I thought it would. I'm trying to use the following two strings:
????1234
compared to
1234????
Shouldn't search find these two strings the same since given the wildcards? Or am I missing something? If I use the above, I receive a VALUE error rather than a match.
I have a problem with the code below. I've borrowed parts of it from various online sources and attempted to make it my own. I need it to go through a column of data that is imported into column A. The unique search criteria are rows that goes sequentially (i.e. 1, 2, 3). The format for the search is (#. text). I need to copy those rows to sheet 2. The problem with the code is that it only copies the first occurrence of the sequence (the row with 1.) to sheet 2 and stops.
Here's an example of the data:
Kalé
1. Kalé
$$ Japanese, Sushi Bars
Shigezo
2. Shigezo
Southwest Portland, Downtown
Code:
Sub Copy_FSE_yelp()
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Finds last row
For i = 1 To LastRow 'creates loop for column
If Cells(i, 1) Like "*" & "." & "*" Then
Range(Cells(i - 1, 1), Cells(i - 1, 1)).Select
Selection.Copy
[Code] ........
When I use a lookup to look for "*"&A1&"*" where A1 contains "AB" I get a value not available error despite the fact that in the postcode table there are loads of postcodes beginning with "AB...". Is it the multiple entries of "AB" in the lookup table that will be messing this search up?
View 9 Replies View Related