I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.

I am receiving an error message for a nested if statement. Excel is telling me my statement contains too many variables.

I am using a VLOOKUP to look up data in a table based upon a result in the first table, however, the entry that is returned is different based upon a unique cell value.

Would anyone have any suggestions on how to shorten this down?

I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.

I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.

There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.

I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...

1.5 O.D. - 16GA - 304SS = $X

but

1.5 O.D. - 18 GA - 304SS = $Y

and such.

The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.

O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.

Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.

I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.

Sub PreviousCount() strDate = "6 June 08" strColumnRange = "$A:$C" strResult = "='" & strDate & "'!" & strColumnRange

Dim i As Long i = Range("A2"). CurrentRegion.Rows.Count Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))" End Sub

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 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.

I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.

I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".

I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)

I understand how to use IF and substitute. its the 7 numbers part i am stuck on.

I could do it in access with the wildcards but excel is different.

Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet

PHONE

WIRE CANDY INDIA

When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this

PHONE WIRE CANDY INDIA

I want it to look like this, removing any blank lines within cell A1

PHONE WIRE CANDY INDIA

Something wrong with the formula? how come it doesn't do both? =SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")

I've got a small problem using VLOOKUP. If the function does not find a value, it returns #N/A. I would like it to say "Not Installed" instead.

The same goes for when VLOOKUP returns a zero - I would like it to return "Installed" instead. Since I've got a great number of rows, I would need an efficent way to change this.

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.

I have a text variable MtgDate containing "25/03/2014"

I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"

I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.

Got as far as:

[Code] .....

but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.

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.

I have the formula in cell A2 =average(A100:A1100). However the range is not always the same so need a formula that will allow me to enter '200' in cell B2 and that will make the formulae in cell A2 calculate =average(A200:A1100). Likewise, if '300' is entered in cell B2 the formula in A2 will calculate =average(A300:A1100).