substitute formula

  1. J

    Substitute All Except

    Hi, everyone, Is there any way to substitute a character or series of characters from a cell with other characters, but with some exceptions? For example, substitute (or replace) all instances of "X" in cell B1 by cell A1, except "XP", "Xbox", "Xochitl", "Xmas", and a few others. I had written...
  2. C

    Inserting formula with a macro

    Hi there, I have tried searching other threads but they were all too old and didn't really answer my problem... I have a command button that when i click it needs to insert a formula into a target cell, i have done this with another button in the same sheet and it works fine but VBA has a...
  3. S

    How do I add leading zeroes in this example?

    I want to take the original naming in the image below and turn it into the required naming format. This includes substituting "/" with "-" and then adding a leading zero to sequence number at the end of the number. Using the 1st column, I used a simple Substitute formula to change the "/" to a...
  4. B

    Search and substitute formula

    Hi all, I cannot get this formula to work. I tried to using wildcards, but those do not work at all. So one cell $J236 for example has this text in the cell: Mechanical Services - HVAC Pipework - Block B First Floor- Strip Out my formula is at it stands...
  5. S

    Substituting SPACE, CF and LF characters with a TAB character?

    Fellow Forum Members, I have text string data that has unwanted Carriage Returns (CF) and Line Feeds (LF) I need substituted with a TAB. I also need all Single Blank Spaces substituted with a TAB. What is making this complicated is how I am dealing with developing a formula that involves...
  6. C

    Substitute Exact

    Hi All i want to be able to find an exact match when using substitute, not sure if possible. I currently have the substitute formula working fine up until cell A10 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E5, A5, B5), A6...
  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. T

    Don't use Googletranslate or MicrosoftTranslate, but SUBSTITUTE formular can do (?)

    Hi all, Could you pls help me how to created more than 64 SUBSTITUTE fuctions in one cell. 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...
  9. S

    Find any stand alone letter and replace with ""

    I have a spreadsheet where users can paste employee census information with varying formats. Is there a formula that would locate any stand alone letter (A-Z) within a cell and replace it with ""? Here is how the names can be formatted: <tbody> Washington,Richard Y (Last,First)...
  10. I

    substitute formula needed

    Need to remove all but one variable string from a cell. Example: K2 Cell contains: "specific text string hereAAAA-BBBB-CCCC assignment..." H2 formula result: CCCC Details: AAAA, BBBB and CCCC represent text strings that are variable in characters and number of characters. Every line begins...
  11. S

    Extract only numbers from cells with different lengths

    I have a worksheet with different values within column A and I am looking to extract all of the numbers only. The values in column A can vary in terms of length and characters (ie $, %, .). I understand my current formula is replacing all "-" with " ", but I am looking for assistance to refine...
  12. G

    IF? formula to change cell if it contains one of several options

    Hello, thanks for any help you might be able to give me. My knowledge of formulas is limited so apologies if I'm not very clear. Our supplier is split into 4 divisions, with each division having multiple short codes for each branch - see below for an example (there can be 30+ short codes...
  13. A

    Substitute to replace value

    Hi, Using =SUBSTITUTE(A:A, "/", "-") to replace all / with a dash. Not getting what I expected. For e.g, 02/02/6451 converts to 1662253
  14. R

    Substitute a Character if it's in the cell at this location

    Hello, I have columns of thousands of numbers and letters combined. I'm trying to do a formula that will check and see if the last 6 characters begins with a "/" character, IF so THEN Substitute it with a "-" (Hyphen). My data has a lot of "/" (backslashes) so the formula has to check only the...
  15. F

    Indirect & substitute

    HI, I have sheet1(called "Timesheet") and sheet2("Legend"). Into Legend I have: <tbody> Category (columnA) Non Game (columnB) Training (columnC) Sport Value1 Training1 Non Game Value2 Training2 Training Value3 Training3 Project Value4 ... Admin ... .. </tbody> Into Manager Name I...
  16. M

    MS Excel: Nesting SUBSTITUTE & CONCATENATE Formula within Text String

    I would like to know if it's possible to combine a nested SUBSTITUTE and CONCATENATE formula or similar to add/insert a Character within text string I have a column containing keywords (Cell: A2:A3) that I would like to place a unique character ("$") within the text string in front of each of...
  17. D

    Standardizing Capitalisation Formula

    Good Day everyone, I'm trying to standardize the capitalisation of products descriptions in an spreadsheet. Seems fairly straight forward but unfortunately the boss wants measurements to stay in lower case but wants to have the descriptions of the products in upper case. What I need to do is...
  18. P

    Remove partial data from cells

    Using Excel 2013: I have a spreadsheet with values in this format. ="10/26" This is not a calculated value. That is how it extracted from another database. I have over 100,000 values in this format so doing them individually not realistic. I need a formula or process to extract only the...
  19. V

    #REF! Error when using INDIRECT to SUM table column

    Hey there, I'm combining 2 different Excel Templates to make a budget, where one sheet is a yearly summary, and the rest are monthly budgets. Each monthly sheet contains tables with the general name tblMONTHCategory, where MONTH is the first 3 letters of that month, and Category are the budget...
  20. mlektra

    Replacing sequencially all characters by a specific character

    Hi guys. Is there a formula here to replace sequencially all characters of a given value by a specific character? Let me show you. I have this value : need to replace one character after one character by the "*" symbol. So it will give me this : * g* go*

Watch MrExcel Video

This Week's Hot Topics

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