substitute

  1. The_Rock

    Random Replace Characters in a Word

    Hi Folks I've been scouring the net but without success. I'm hoping the genius's here will be able to help me :-) I'm trying to mock up some work for my daughter to help her with Synonyms and Antonyms. What I would like to do, if possible is: Column D looks at Column F (which will be hidden)...
  2. V

    Split variety of full names into prefix, first name, middle name, last name and suffix using a formula

    Hi, I have a list of thousands of names that need splitting into columns: prefix, first name, middle name, last name and suffix. The full names are varied and therefore the formulas I have tried thus far do not work on the whole list. Formulas I have tried thus far: Prefix...
  3. L

    (substitute, replace) VS replace tool

    Hi I know how and when to use substitute() and replace() function. But my question, I found that Replace tool is doing the same. Is there any case that Replace tool is not going to help then I will have to use substitute() or replace() functions. Thank you very much.
  4. C

    VBA Formula in Loop Where Main Reference is Variable

    Hey everyone, I'm working on making a macro that will organize data for a mail merge. The main table is contains 62 lines of data for each person, with the first cell containing multiple rows. I'm using substitute to get around this. If we aren't in VBA, the formula would be 'Output'!A3 =...
  5. J

    Excel formula (not VBA) to extract delimited string?

    I have the abstract string "firstString/secondString/thirdString/fourthString" in C2. I want to use a single Excel formula (not VBA; no helper cells) to extract "secondString". Any ideas? Note that "*String" is any arbitrary sequence of characters, not including "/". So, the formula must...
  6. P

    Problem replacing/substituting a part of a String variable with a long text string

    Hi I have a macro which runs some SQL code to update a table on a server. The code and SQL has run fine in the past, but I've hit a road block now, seemingly because SUBSTITUTE doesn't seem to like swapping in a really long string (~35k chars) for a short string into a STRING variable. If...
  7. V

    Assistance with Formula

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

    Substitute Alphanumeric Values

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

    how to substitute a column reference for an If statement

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

    SUBSTITUTE a Blank space...

    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!
  11. G

    SUMPRODUCT and SUBSTITUTE

    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!
  12. J

    TRIM in VBA with Substitute

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

    Using Wildcards in Substitute function

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

    If #value! In an array then substitute #value!?

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

    Proper and Substitute

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

    PowerPivot Substitute Cell Reference in Measures Portion of Cube Value Formula

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

    SUBSTITUTE function overload - more than 64 levels of nesting

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

    Lookup Numbers & Result in Corresponding Text

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

    Sum of a set of cells multiplied with substitute returns 0 or #VALUE

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

    How do i use Thisworkbook.path with this VBA

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

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top