substitute

Hello, I need to build the following formula, in column A I have legal name and in column B preferred name. If the Legal cell is blank, I want to return in C the value from B with two other conditions. Say in A1 the value is blank, in B we have D.O’Brien. What I need in C is D O'Brien I need to...

Hi, Is there any other alternative or shorter solution for rewriting the below formula example.i have read in an older post that the SUBSTITUTE function cannot handle the "old_text" part as an array{1,2,3,4,5,6,7,8,9,0} Thanks In Advance. <tbody> A1B2C3D4E5F6G7H8J9K0...

Hi sht = Me.ComboBox15.ValueIf Sheets(sht).Cells(i, "A").Value = (sht) Or _ Sheets(sht).Cells(i, "A").Value = productCode Then Me.TextBox13 = Format(Sheets(sht).Cells(i, "C").Value, "0.00") I have the above Syntax that looks up a value on a worksheet, it works fine, but I would like to...

If I have a cell with two digits - how can I use the SUBSTITUTE function to add an extra character to make the cell have 3 digits? So a cell with 11 would turn in to 011 or A11 Thanks for your help!

I have the formula =SUMPRODUCT(SUBSTITUTE(0&B15:B80,"CA$","",1)+0,--($A$15:$A$80=$P$16)) it breaks #VALUE ! when it hits a cell in column B containing the string (CA$123). Column B contains CA$1234 and (CA$123) values. I need the formula to SUBSTITUTE both if possible. Thanks!

Hello, I have searched and t this point have not found anything that is very helpful with the substitute function in VBA. I am unsure why but sometimes TRIM does not remove the space after a the word in a cell. Someone gave me the following which seems to work...

I have tried to use the Substitute function to replace part of a file. I need to do this in VBA with a UDF or something. I want to search for this string (the characters between < employeeid >< /employeeid > could vary - that is the wildcard part) < employeeid...

Consider the following table. <tbody> A B C 1 1 =$A$1:$A$5 2 2 3 =VALUE("THREE") 4 4 5 5 </tbody> If the range is taken from: $A$1:$A$5, and that range is represented as an array in B1 using the formula entered: =$A$1:$A$5; which is equal to the array: {1; 2...

Hi all How can I combine a Proper and substitute formula together?

I have an interesting one here. I'm looking to try and substitute the text from a cell into the measures portion of a CubeValue formula in Excel. The CubeValue references data from a related sheet in PowerPivot and I'm bringing over multiple columns of numbers so it would make life easier if I...

Hi all, I created a formular to translate a sentence with many Vietnamese words inside one cell at loaciton: L3, This SUBSTITUTE formular can be use to copy and paste to other cell for translaiton the next cells. but this SUBSTITUTE fuction has limited in 64 levels of nesting (?). it mean that...

Hi All, Is it possible to lookup a string of numbers in any cell from other workbook and result in the corresponding text (Excel 2007) Thank you in advance. For example: <tbody> Lookup Cell Result 1, 2, 4, 12, 14 Relay burnt, Capacitor Burnt, Suction Joint Gas leak, Overload burnt...

Hello I would like to know where my logic is faulty since Excel returns or 0 or #VALUE as a result. I would like to transfer the following in a formula using INDIRECT and ADRES: =SUM(T9:Y9*T6:Y6) Some additional details: The column letter can be created using indirect itself (row number...

Hi, i have some code which works fine with a set URL but i want to substitute it to use the workbook path ThisWorkbook.Path & "\MYDB\DATABASE1.mdb" the code below Sub getData1() dat1 = Range("I2").Value MYSQL = "SELECT * FROM `C:\test\MYDB\DATABASE1.mdb`.TABLE1 TABLE1WHERE TABLE1.EDAT="...

Is it possible to use SUBSTITUTE (or some other method) using a list of inputs? For example, my target cell contains the string <tbody> I love Apples, I hate Orange, my Banana, my Grape </tbody> and on another sheet I have a ranged list: <tbody> Apple Red Orange Orange Banana...

Overview: I am trying to run a mass substitution of a large list of terms by looping a Substitute function via VBA. Details below. and a link to a possible idea for a solution I found elsewhere is below the image here. Perhaps can help. I have a list with two columns in my 'Job Title Append'...

Hi I have cells as below. I know clean() function will not clean these chars and I will need to do substitute() these chars with "" for example. But I have 2 questions: 1) does that mean I have to create a new substitute() function for each cell? knowing these cells are having different chars...

I wonder if you can help. I'm looking for some simple VBA to allow me to loop through column A in a worksheet. If the cell in column A contains any data, then format its adjacent cell in column B. Also, should that adjacent cell in column B 'not' have any data to format, then continue to loop...

Need help for substitute formula using info in quote.

I need a formula that does substitute ALL "" with "" (including the > character) <*span style..** .><*div style=...* *> <*p style..** .> <**div* *> <*/div* > (I added asteriks and spaces otherwise the tags cannot appear :P Find and replace works for the last 2 but not the others at what...