1. L

    substitute within formula, not string

    I have several months' of data in a row, e.g. Col A Col B Col C ... Col U Col V Col W 1 Feb 2022 2 Feb 2022 ... 28 Feb 2022 1 Mar 2022 2 Mar 2022 ... Row 15 Type A 9984 9182 ... 9374 9571 10012 ... Row 16 Type B 24017 23981 ... 24231 24523 24917 ... I refresh this data every...
  2. T

    Substitute, then substitute something else

    Okay, i have no idea what I'm doing. After some toying, i have =IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A4,FIND(" ",A4&" ",FIND("@",A4))-1)," ",REPT(" ",LEN(A4))),LEN(A4)))," ") in order to extract email addresses from stuff like "Bhatia, Tulikaa <>" My problem is that i...
  3. S

    Convert a 12 month date to a single cell

    Hello, I have in cell A2 Date in format 18-Nov-2020 UTC I Need format 18.11.2020 in cell B2 I created this formula, but it probably won't be the right way: =(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"Nov","11"),"-",".")," UTC","")) Dates change, so Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun =...
  4. U

    Want to fill and swap values in the cells and column

    I have a excel table in the form below: Level1 Level1- Level2 Level2 Level2- Level3 Level3 Level3- Level4 Level4 a b c d f i j b a d e g g k b e c h d i The Level1 is parent records, Level2 is child records, Level3 is grandchild records and so on... Level1-Level2 is...
  5. E

    Sheet Name But Referencing Column Letter

    Hi, Overall I'm trying to use a filter function with data from a separate sheet. Problem is the data is not very consistent and the column headers move around. I can use the address and match to find my column letter in the other sheet but I want to be able to put it back in to an overall...
  6. E

    Substitute error

    Hello Guys, I m trying to get this forumla below working but i keep getting this error: Unable to get the Substitute property of the WorksheetFunction class Range("M" & cell.Row).Value = Right(cell.Value, Len(cell.Value) - Application.WorksheetFunction.Find("@"...
  7. L


    Hi all, I am using a formula which uses LEN and SUBSTITUTE. The formula works (I didn't learn it by building up, I got it from a tutorial), and I understand what LEN and SUBSTITUTE do separately, however, I don't understand what they are doing in this formula. I was hoping anyone would explain...
  8. Blacksmith

    Many Substitutes on single cell of text

    Hello all, First poster and total excel novice here. I've recently been put in charge of a filing system at my workpace, and the software which was previously used was massively out of date and required lots of repetitive work from users. Over the past few weeks I've been iterating on an...
  9. R

    Search and return matches into multiple columns

    Hi All I have a scenario where I'm attempting to extract certain words that appear between double quotes but only if they meet a certain condition. For example I want to search for ALL fruit in column A and present these individually in the subsequent columns. Fruit' = "Pear" OR 'Fruit' =...
  10. 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)...
  11. 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...
  12. 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.
  13. 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 =...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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!
  20. G


    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!
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back