# Nested IF: Substitute Three Variables

Dec 10, 2008
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.

View 2 Replies
ADVERTISEMENT
Nov 4, 2008

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," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))

View 9 Replies
View Related
Nov 5, 2007

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?

=IF(CR="AG",IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,3,FALSE))),

IF(CR="G",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,4,FALSE))),

IF(CR="MG",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,5,FALSE))),

IF(CR="C",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,6,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,6,FALSE)))),

IF(CR="UC",IF(ISBLANK(A7),"",VLOOKUP(ISNA(VLOOKUP(A7,SI.xls!$A:$G,7,FALSE))=TRUE," ",VLOOKUP(A7,SI.xls!$A:$G,7,FALSE)))))))

View 9 Replies
View Related
Apr 29, 2013

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 = 3 Rm= W*log(K)

.6

View 9 Replies
View Related
May 14, 2008

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"))

View 2 Replies
View Related
May 5, 2014

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.

=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),

’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),

’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost

[Code] ......

Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?

View 2 Replies
View Related
Jun 20, 2008

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

View 4 Replies
View Related
Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies
View Related
Oct 2, 2008

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,

View 11 Replies
View Related
Feb 7, 2012

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?

View 3 Replies
View Related
Oct 2, 2007

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?

View 9 Replies
View Related
Aug 19, 2008

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.

View 9 Replies
View Related
Nov 6, 2008

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.

View 9 Replies
View Related
Oct 19, 2009

Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?

Here's my ....

View 9 Replies
View Related
May 17, 2014

I've got a workbook with multiple formulas e.g.:

t=S/D

TCh=Ch*(S^2/2D)*(D/Q)

TC=Co*(D/Q)+Ch*((i-D)/2i)*Q

which are built by implementing the symbols:

t, S, D, TCh, Ch, Q, TC, Co, i

I'd like to substitute the symbols in following sequence:

t = a

S = b

D = c

TCh = d

Ch = e

Q = f

TC = g

Co = h

i = x

Questions:

1) Which formula could substitute the symbols automaticly?

2) If there are up to 100 formulas in the workbook in the range of A1:E100,-

would it be possible to to set the substitution for the mentioned range?

See the attachment.

View 5 Replies
View Related
Jan 11, 2014

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.

Substitute.png

View 7 Replies
View Related
Feb 9, 2009

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.

View 14 Replies
View Related
Jun 28, 2009

Adding second substitute command

What I need to do is change this:

View 2 Replies
View Related
Jul 10, 2007

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,"***************","**/**-**-***-****/*")

View 10 Replies
View Related
Nov 17, 2011

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),"")

View 1 Replies
View Related
Aug 6, 2012

if i obtain an eqn by plotting a graph in excel, etc,

y = 1.22952117E-06x3 - 1.08197863E-05x2 + 9.97531712E-01x + 3.99014495E-01

and i wish to subsitube a cell value from another sheet into the 'x' in the above eqn and i need to do this 20 times.

View 3 Replies
View Related
Jul 9, 2008

MY DATA

LIST1 "long" strings

LIST2 strings with characters from LIST1: the order may differ

NEEDED

the expected result is the "substraction"

all characters form LIST1 which are NOT in LIST2 should appear in LIST3

COMMENT

to make it easier to read, I have put the same string in entire LIST1

EXAMPLE

LIST1LIST2LIST3ABCDABCDABCDBCADABCDCBADABCDACBDABCDADBCABCDABCDABCDADCA

View 9 Replies
View Related
Jul 14, 2009

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.

View 9 Replies
View Related
Mar 2, 2007

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.

View 12 Replies
View Related
Aug 25, 2009

I've got a bunch of formulas with cell references in them.

I've gone back and assigned names to those cells.

Is there a quick and easy way to update all the formulas with the cell names now that I've assigned them?

View 9 Replies
View Related
Mar 25, 2014

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.

View 2 Replies
View Related
Sep 30, 2011

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?

View 9 Replies
View Related
Oct 28, 2012

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

View 8 Replies
View Related
Jun 16, 2014

I have the following structure in values found within a large dataset

6A-6200 A

6A-6156-A

I used =SUBSTITUTE(CELL," ","") to remove the space in the first value: Works fine

I am trying to remove the 2nd occurence of the - in the second value: Desired Result 6A-6156A

And how I may be able to complete both within one formula.

View 3 Replies
View Related
Jun 17, 2006

I have a problem to achieve the above subject, as per the attached picture.

View 2 Replies
View Related