substitute

Hello, I am trying to find a substitute for a dynamic column name within my sumif formula so I don't use the volatile indirect command. How can I make a reference in a more optimal way so my file isn't so slow due to indirect? The column name changes from Budget_1, Actual_1, Prior_1 for...

Hello, In my score sheets, there are often blank spaces left in between other cells that need to be zero. I know of the find/replace function that can substitute blanks with zeros, but that will substitute any blank cell, even those that are after (to the right of) the last filled cell in each...

Hi! I need all text after an identifier "CPAL", which can vary in length and may have a : or ; after "CPAL" plus a space. Example Text: 1 0 Retrofit A/C, 2/4/1/0.5 FOC IP, CPAL: HSE + W + B Parts + Structures What I am trying is giving me the text before that identifier...

In Power Query I am connecting to my Outlook account to collect data from the Calendar. All is fine using the syntax Exchange.Contents("abc@xyz.com"). To enable this to be shared with other users I would like to enter the email address in a cell in the spreadsheet and pass it to the query. I...

Hello, I'm using substitute nested many times to turn formulas with cell references into equations with text. It's a multipart process starting with a formula-to-text function generating a secondary array, some other stuff, and then a huge string of nested substitute functions generating a...

Good evening Trying to utilize substitute in VBA. Below is small example of my data. Column B is the substituted data. Substitute TD for TR SP for SK DQ for DW Thank You <tbody> TR01 TD01 TR02 TD02 SK01 SP01 SK02 SP02 DW01 DQ01 DW01 DQ02 </tbody>

Is it possible to nest SUBSTITUTE within an IF formula? I have a column that has a range of numbers in it that I want to combine in a TEXTJOIN string. However IF the number is a decimal, I need to substitute the decimal with an underscore. i.e. instead of 6.875 it needs to be 6_875. I'm not...

Hi, is there a faster substitute for SUMPRODUCT formula? Went through some forums etc. and found only that SUMIF could be a substitute. But I can't get it work for me, I guess probably because I have 'transpose' in 'sumproduct'. The formula I want to speed up...

I have the following formula enter in to "A2": =SUBSTITUTE( (SUBSTITUTE( ("$GR$1"), (1), ("") )), ("$"), ("") ) ; is there a way to make both nested substitutions of "1" and "$" with a single substitute function?

Hello, I'm using substitute nested many times to turn formulas with cell references into equations with text. It's a multipart process starting with a formula-to-text function generating a secondary array, some other stuff, and then a huge string of nested substitute functions generating a...

I am trying to make user friendly names into database friendly names. I have a list of 2000+ field names that I've been trying to cut down to a format I can use for our database. For example: "Drawings Issued" >>> "DWG_ISSUED" "Quote Received" >>> "QUOTE_RECEIVED" "Project Manager" >>> "PM"...

Hi all, I've searched the web for an answer so I'm hoping this can be done. Basically I am creating a BOM (Bill Of Materials). I am ordering cable in pre manufactured lengths. The cables basically has the same part number A0090CEGAC0BUXXXM the XXX represents the cable length. I also have to...

i have text in col B1:B101 How can i build the formula in A1 and copy it down by using substitute function to substitute multiple text at once ==SUBSTITUTE(B1,"1st floor" & "2nd floor"&…So On,"") OLD TEXT is multiple new text will be ""

Hi I want to get rid of all numbers in a text. So I thought of used nested substitute() function and nested 10 times go get ride of 0-9. I have a column with house address and i want to keep street address not the number and then autofill this function. Do you think there is better idea to do...

Hi all, I have a block of text in a cell, this block will contain 1 or more dates. They may be formatted as January 2017 or 01/01/2017. I would like to write a formula to replace the first style with JAN2017 and the second style 01JAN2017. I am trying to work out a way to do it combining...

in one cell (say A1) I have a word, example - apple in the next cell (say A2) I have a sentence, example - An APPLE a day keeps the doctor away is it possible to render the following result in another cell (say A3)? - An @APPLE a day keeps the doctor away I have introduced a character...

in one cell (say A1) I have a word, example - apple in the next cell (say A2) I have a sentence, example - An APPLE a day keeps the doctor away is it possible to render the following result in another cell (say A3)? - An <span class="highlight">APPLE</span> a day keeps the doctor away please...

Hi All I am sure this is possible, so hopefully you can help I am trying to translate UK English To US English for a program that uses Excel for content translations 1 sheet has all my content in cells (sentences) Another I have a column "A" UK words and "B" US Words I need a formula please...

Hi All I am sure this is possible, so hopefully you can help I am trying to translate UK English To US English for a program that uses Excel for content translations 1 sheet has all my content in cells (sentences) Another I have a column "A" UK words and "B" US Words I need a formula please...

Good Morning, I am trying to hyperlink to google maps but in doing so I am also trying to substitute two values (postcodes) on my spreadsheet into the address from and the address to. For example: =HYPERLINK("https://maps.google.co.uk/maps?saddr="&SUBSTITUTE(C8,"...