Using The "substitute" Function

Dec 16, 2007

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:

View 11 Replies


ADVERTISEMENT

Using Substitute Function In VBA?

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

Wildcards In Substitute Function

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

COUNTIF Function After SUBSTITUTE Function

Jan 3, 2012

I 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?

View 9 Replies View Related

SUBSTITUTE Function To Replace Commas

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

SUBSTITUTE Function With Wildcard For Symbols?

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

Substitute Function Non-case Sensitive

Nov 2, 2006

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.

View 3 Replies View Related

Substitute Function With Find, Mid & Left

May 25, 2007

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

View 4 Replies View Related

Substitute Function Giving TEXT Output?

Oct 2, 2012

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?

View 2 Replies View Related

7 Nested Functions Alternative - Substitute Function

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

Indirect Function And Substitute In Order To Create Conditional Dropdown Menus

Jun 26, 2014

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.

View 6 Replies View Related

Mid Substitute Formula

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

Substitute Apostrophe

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

Substitute For Filesearch

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

Is There A Substitute Character That Can Be Used

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

=SUBSTITUTE In Email

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

Formula To Substitute Given Symbols

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

Substitute Text String

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

Using Substitute When There Is Text Followed By N Digits

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

Adding Second Substitute Command

Jun 28, 2009

Adding second substitute command
What I need to do is change this:

View 2 Replies View Related

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 View Related

How To Use CONCATENATE And SUBSTITUTE Formula Together

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

How To Substitute Cell Value Into Equation

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

Fuzzy Substitute ABCD - AC = BD

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

Substitute Error Values

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

Substitute Names In Existing Formulas

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

Using Substitute To Change Text In Variable?

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

Substitute A Number Into Cell Reference?

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

SUBSTITUTE With Conditions - Large Dataset

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

Replace Or Substitute Inverted Commas With Nothing

Jun 17, 2006

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved