substitute formula

Hi, So basically I have 4 main possibilities: PYMT769291 INV 769291 INV 769291_1 -> This could be _2 or _3 or _4... INV 769291BD And I need 1 formula that makes them all convert into this: PYMT769291 -> This one should never change 769291 -> This one gets rid of the...

I have a formula that replaces all spaces in a string with a dash "-". The problem with this is, is that when the original string already contains a dash between spaces " - " the endresult now is "---" I would like to modify my formula to prevent this from happening and in those cases just...

I have a column of sheet tab names in Column A. I would like Column B to reference cell G44 in each corresponding sheet tab. This is the code I am using. However, it is giving me a reference error. Does anyone have any idea what I am doing wrong? =INDIRECT((SUBSTITUTE(TRIM(A6),"...

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

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

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

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

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

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

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

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

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)...

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

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

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

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

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

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

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

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