Multiple Substitute Or Replace Text Using A Table?
Feb 11, 2005
I'd like to use VBA to create a super substitute function. For my needs, nesting is insufficient because my substitution list is at 20 and growing. To make matters worse, the function needs to be used in several places.
What I'd like to do is have a named table with two columns for the function to use as a look-up for potential substitutions. The first column would contain the original text and the second would contain the replacement text. This way, whenever new items come up, all I have to do is add them to the list. The syntax of the function would be along the lines of SUPERSUB(TextString, table), where TextString contains the text that could be modified.
I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.
Value Real Value (after substitute function) 0,1831 0.1831 23,3333 23.3333 12,5199 12.5199 5,5000 5.5000 20,5999 20.5999 24,4671 24.4671 200,0000200.0000 2,5386 2.5386 0,4000 0.4000 1,5019 1.5019
how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.
I am using excel 2007. I have a worksheet with a list of words I wish to step through this list replacing the selected words in another workbook with nothing (ie deleting them).
I have the following code
Code: Sub replacewords() Dim MyWord As String Dim wbLibrary As Workbook Dim wbWorking As Workbook Dim myExcelColumns As Integer Dim myExcelRows As Integer Dim MyRow As Integer Dim MyRange As Range
I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".
I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)
I understand how to use IF and substitute. its the 7 numbers part i am stuck on.
I could do it in access with the wildcards but excel is different.
I have a text variable MtgDate containing "25/03/2014"
I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"
I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.
Got as far as:
[Code] .....
but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.
=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890 =ISTEXT(A2) in cell 'A3' gives me: TRUE
BUT, =A3+1 gives me: 1234567891
Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?
In cell A1, I have three possible text strings: (1) "change/s: changed the color green to red", (2) "change/s: changed the color from green to red, changed the size from big to small", or (3) "changed the color from red to green".
I need a formula that will look at a text string, and if there is a comma in the string, it finds the word "change/s" and substitutes it with "changes", if it finds no comma, it substitutes the word "change/s" to "change", and if the word "change/s" is not in the text string at all, it leaves that text string unchanged.
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
I have a spreadsheet I work with which contains a formula that needs to take an item ID letter/number combo, substitute the preceeding letter(s) with leading zeros (if the initial numerical characters are fewer than 5) to make the result return a 5-digit number.
The original ID can be in several possible formats (beside each I will post the desired result): V7684 07684V366 00366V88 00088V60827 60827VS57871 57871VS67 00067
I have the following formula which works like a charm for all except the last example: =IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS","),"V",")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","0"),"V","0"))))
If the first 2 characters are letters ("VS") and there are fewer than 5 numerical characters, the formula results in a 4-digit number, rather than 5. Using the above example, VS67 returns 0067 instead of 00067.
P.S. I just tried =IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS",""),"V","")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","00"),"V","0")))) which only works when there are 3 or fewer numerical characters.
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
I have an existing Cash Flow Report that has a column of abbreviated/shortened (WBS Element) title where each cell contains a unique three lettered/numbered amount of characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Element) title (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBS Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru. B11, just to the left of its abbreviated/shortened (WBS Element) title.
I have an existing Cash Flow Report for my work that has a column of abbreviated/shortened (WBS Element) titles where each cell contains unique three lettered/numbered characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Elements) titles (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBA Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11, just to the left of its abbreviated/shortened (WBS Element) title.
Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx
I am trying to find specific text throughout an excel document (2007) and replace it with the same text but in italics. I tried using the options/format function and selecting italics for the "replace" text but it replaces the entire cell text in italics instead.
I have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):
Column Dtext to text help.xlsx BIRD FISH DOG BAT BUG
I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:
FEATHERS FISH TAILS BAT BUG
All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.
Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.
I'm trying to get excel to check a cell against a table of numbers and if it finds any of them to replace it with the correct number.
Basically if the cell contains any of the numbers in the "Do Not Use" column then it should display the corresponding entry from the "Replace With" column?
see attached spreadsheet. Cells in orange are where I want the formula to go...
I'm trying to convert our commonly used pivot tables into formulas based static tables. I've made great use of DSUM for most of them, put I'm not sure if this is an option for this one.
I've attached a sample of the spreadsheet I'm working with. I need it to search for the specified Area, Account and Month, and then give me a list of companies and their subcategories, and add together the totals for them. My problem is that I don't know what companies will be used each time. I can build a list of commonly used ones, but that won't help if one month we use an oddball one. Pivot tables aren't an option because size is an issue.
Trying to Find a specific word and Replace with another within a textbox ( created from the drawing tool, as well as the control toolbar) The textboxes contain loads of text information.
as an example The word "Apple" to be replaced with "Orange"
I came across this code from the archives (compliments of Dave Hawley)
Sub ReplaceTextBoxText() Dim sTextBox As Shape Dim wSheet As Worksheet Set wSheet = Sheets.Add() For Each sTextBox In Sheet1.Shapes
I've tried several variations with no results. (perhaps it's to extensive for my modest needs)
We have a list of typed information (see attached) in which we would like to remove some words and simply replace with a space "". In addition to this: Unfortunately, folks tend to mistype or misspell words, add extra digits, etc, and for those mistakes we would like to replace them with corrections. Therefore, for this problem, we have a Replacement Table and the Subject Data, an example of which is shown in the attachment. I have posted a slightly similar problem on Ozgrid recently:
Replace Words In Cell Matching Those In A List and in that link, the following UDF was proposed:
Function ReplaceIfInList(rList As Range, rCell As Range, _ strReplacement As String) As String Dim rLoop As Range
If rCell = "" Then Exit Function For Each rLoop In rList If ReplaceIfInList = vbNullString Then ReplaceIfInList = Replace(rCell, rLoop, strReplacement) Else ReplaceIfInList = Replace(ReplaceIfInList, rLoop, strReplacement) End If Next rLoop End Function
This worked great, but at the time i posted this, I did not have the foresight to anticipate the flexibility in what the replacement text would be (the above code always replaces with a ""). THIS new Post is asking the following:
Is there a way to define (or modify the above) User Defined Formula that will analyze the words typed in a cell, which are typically separated by spaces (or on accident, multiple spaces), and essentially use the Replacement Table as a reference for what word to replace with?
I have a worksheet with 10 columns and aprox. 40.000 rows. In column C i have something similar to:
AAA (4102) XXX (4104) FFF (4106) MMM (4341)
for every row.
I need to replace this different numbers and the parntesis, so i want to replace "(4102)", "(4104)","(4106)", "(4341)"... I have 90 different possibilities of numbers and I have been using this
what = InputBox("word to search") repl = "" Cells.Replace what:=what, Replacement:=repl, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
I have a single column with more than 200.000 cells. In each cell there is a small image obtained from a website. There are four different images: red, orange, yellow and green . What I need is to replace all equal images by an equal string, such as (red square) =Q4, (orange square) =Q3 and so on, or, at least, place a string for each image type in an adjacent cell
I am importing content control information in a word file into excel by using a macro in excel. The information is coming across nicely but when the information about a checkbox comes across, in excel, that's what you see, a checkbox (☐ or ☒). Is there any way (formula or vba) to replace all the checkboxes with a yes or a no?
I wrote a formula that works:
Code: =IF(G8="☒","YeS","nO")
But this will add this information to a new column and I would like to replace the information in the existing column (the form has about 200 checkboxes) and I don't want to add a bunch of new columns.
I also toyed with VBA:
Code: Range("G1:G100").Replace "&", "No"
But when I try to paste the ☐ in the vba code, I get a "?" so I am assuming it doesn't recognize the ☐ as a valid character.
I would like to create a very simple Excel file that makes the following:
If I enter 2 in a field I want that 2 replaced with "07.15-19.00" and also a 1 entered to the field next to it. If I enter a 4 I want the 4 replaced with 00:00-08:00 and the number 8 entered in the field next to it.
Is there away to replace text automatically in excell from a list. For example:
I want a list stating..
Hotels in ... Acomadation in ... Stay in...
Followed by:
England US Germany
Is there a way of doing this automatically without having to copy and paste the first list 3 times then find and replacing indivually for England, the US and Germany?
how to write a search and replace macro, but instead of writing:
Cells.Replace "OldText", "NewText"
how to independently read this "search" text into a string (hopefully a string that can handle 'more' than 255 characters per cell, even in Excel 2000) then replacing that search text("OldText") with ("NewText") step-by-step?
