Replace With The Right Function
I was trying to help another user and came across this problem.
In cell A1 I have "abcd"
In cell A2 I have =REPLACE(A1,4,1,"e") resulting in "abce"
I tried to use = REPLACE(A1,RIGHT(A1,1),1,"e") but get #VALUE!
I am wanting to use RIGHT because the number of characters is unknown.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Catching Errors When Using The Find / Replace Function / Replace All
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
View Replies!
View Related
Replace A Forumula With A Vb Function
I've always used event driven functions - ie. user clicks a button and my function gets called. What I want to do now is replace a HUGE formula that is impossible to debug with a function. So the idea is, any time the user changes any cell, the function should be called and update a certain cell with a new value.
View Replies!
View Related
Replace #N/A In Match Function
=IF(ISNA(INDEX($D$3:$D$100,MATCH(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0)),"",(INDEX($D$3:$D$100,MATC H(G19,IF($C$3:$C$100="DF",$A$3:$A$100),0))) what i'm trying to do is to replace #N/A with blank. Taken the above from vlookup examples but does not seems to work.
View Replies!
View Related
Replace Function In VBA
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website: [url] Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.
View Replies!
View Related
SUBSTITUTE Function To Replace Commas
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.
View Replies!
View Related
Replace INDIRECT With INDEX Or Another Function
I am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet. On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines: =IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!$C$2 & 'Detailed net pay calculations'!D1 & "'!$B$43:$Y$52"), 4, FALSE), “Do something”, “Do something else”) The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!$C$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1). My query The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).
View Replies!
View Related
Find, Replace Function: Formula Is Too Long
I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.
View Replies!
View Related
Creating A Function Or Macro To Replace Data Between Cells.
I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22. What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22. Is this possible?
View Replies!
View Related
Function "replace" Missing For Mac Vb
trying to transfer a macro from Win XP, Excel 2003(?) to Mac OS X, Excel X, my program crashes due to missing functions in Excel X. Apparently, there is no such function as "Replace()" in Mac Excel.... 'these lines do not work on Mac Excel lfdNrStr = Replace(lfdNrStr, "(", "") 'takes out the brakes lfdNrStr = Replace(lfdNrStr, ")", "") 'takes out the brakes lfdNrStr = Replace(lfdNrStr, " ", "") 'takes out the empty spaces lfdNr = CInt(lfdNrStr)
View Replies!
View Related
Find, Replace With And Then Replace Adjacent Cell
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
View Replies!
View Related
Search And Replace Replace The Whole Cell
I have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information. I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.
View Replies!
View Related
Replace, Copy Then Replace Again
I used the following code to replace " characters to !%. Then I copy the selected cells, and replace the !% back to "-es. The copied cells contain the "-es. When I comment the second replace, the function is working correctly, the copied cells contain the !%. What's the problem of my code? Selection.Replace _ What:="""", Replacement:="!%" Selection.Copy 'Selection.Replace _ ' What:="!%", Replacement:=""""
View Replies!
View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place. Is someone aware of a function which will target a relative location instead of an exact cell?
View Replies!
View Related
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3). My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime. My Script: Function Position(rCell As Range, Optional RightPosition As Boolean) Dim vResult Select Case rCell.Text Case "QB" vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2) Case Else vResult = "Invalid Position" End Select If RightPosition = True Then Position = vResult Else Position = "Position not valid" End If End Function So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas). My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View Replies!
View Related
Replace With 0
I am in need of a quick macro. I am not sure how to even write this one. I need it to look at all the cells in the worksheet, and if it is an an error (such as #DIV/O, #NUM, #N/A, etc) replace it with a 0. Is this possible through VBA? Even better, how can I make this an Add-in?
View Replies!
View Related
Replace #N/A With Value 0
I have a dataset. Some cells have values in them while other cells have #N/A. I want to convert all cells that have #N/A to 0 without touching the cells with values in them already (So I can sum rows up without it returning #N/A). I tried if(A1="#N/A",0,A1) but that returns #N/A also .
View Replies!
View Related
2007 Right Function With Embeded Find Function
I have a range of cells, for this example I will use 2. Cell E17 = 77/170 Cell E18 = 8/9 Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18))) This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170" Now if I use: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1)) It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added. Using the formula: =SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
View Replies!
View Related
Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is. The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more. I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).
View Replies!
View Related
Find And Replace ....
I was wondering if it is possible to do this unique find and replace that is explained below. I have many columns with data from our database. I have one column that has my html layout in it. I want to find within the html text data and replace it with data from another column for that row. Example: We have [[manufacturer]] in the html area that we want to replace with data from our column called Manufacturer. So, it will replace the text in the html named [[Manufacturer]] with that rows Manufacturer data we have. Is that possible to do?
View Replies!
View Related
Replace First X Digits
I've recorded a macro to replace the Australian telephone number area codes (at the beginning of each phone number) with international dialling codes. I also need to replace the first two digits ONLY of mobile ( cell) phone numbers which, in Australia, all begin with "04" (see last part of macro code below - column L:L). With the code the way I've recorded it, if it finds "04" in another part of the number (e.g. 0411 104 111), the second occurrence of the "04" will automatically be replaced with "614" as well which I don't want. So I need some code to add so that the macro only searches and replaces those first two digits in that column. I hope I'm making sense?! Sub ConvPhNo() ' ' ConvPhNo Macro ' Macro recorded 12/12/2007 by xxxxxxxxx ' ' Columns("C:C").Select Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _ ...........
View Replies!
View Related
Replace The Values
I have a workbook with two worksheets. The first is called Input and in cells C3 a product code is entered and in cell D7 the value and F10 the description. The second sheet called History has a range of Product codes in cells AA5:CD5, AA4:CD4 holds values and AA8:CD8 descriptions. Now for the intersting part, I need a macro to run that looks at the value in cell C3 of the input sheet and if a match can be found in cells AA5:CD5 of the History sheet then the value from D7 would replace the value in AA4:CD4 and F10 would replace the value in AA8:CD8. Example if cell C3 has RJ12345 as the product, D7 reads £15.10 and D7 reads towel. The code RJ12345 is foound in cell BC5 then cell BC4 would be replaced with the value £15.10 and BC6 would have the worf towel entered.
View Replies!
View Related
Compare And Replace ...
I have 2 Excel files that have 2 columns in each. Product# and Price. The Product#'s in the 1st sheet are in correct order and have outdated prices. In the 2nd file the Product#'s are same but they cannot be sorted the same way as the 1st file. So I cannot just copy the column of updated prices from 2st one and paste it into the 1st. So, my best bet would be to run a script or something that will get a Product# from 2nd file and find it in 1st, if it matches then copy the new price to the 1st file.
View Replies!
View Related
Find And Replace ..?
I have a list of sales associates that are assigned numbers. For example, John Doe is 1022. The data I have to work with only shows his number 1022 but for my reports management wants the name John Doe. I have about 60 sales associates and have a VB code that 'finds and replaces'. The code is huge. It has slowly grown out of hand, with more sales associates added daily. I also have 22 sheets in one workbook with specific columns to search for the numbers to replace with the names. This is what I would like to do. Have a sheet named associates, as more associates are added I would just have to add their name and number. Have two columns with headers column A - Associates column B Associate #. Name ranges for the two columns and write a code that says look in the sheet associate, at named range 'numbertoname' and if the current sheet 'total sales' I am in has the number 1022 in column C replace it with the name John Doe. It seems like it should be easy but.......I have tried and tried today.
View Replies!
View Related
Replace Character With Another
I'm trying to go through a short list of letters and change the contents of every cell in the list that contains a "c" into a "M". I've written this code, but I get a #Value! message on my spreadsheet when I run it: Function FindCpmDpm(SearchRng As Range) Dim CelRng As Range For Each CelRng In SearchRng If CelRng.Value = "c" Then CelRng.Value = "M" End If Next CelRng End Function I am having trouble accessing the cell which contains the "c".
View Replies!
View Related
Replace The Letter
=IF(R16="A",D11,IF(R16="B",G11,IF(R16="C",J11,IF(R16="D",M11,IF(R16="E",P11,0))))) i want to replace the letter A,B,C,D,E, with numbers from 1-5. When I take of the """" and replace the letter with the numbers the formula does not work.
View Replies!
View Related
Compare And Replace
My problem description is as follows I have to create a merged file from two input files.These are 1.The first input file is generated by the tool.This file contains the error message description stored in columns.For the coresponding error message a unique identifier is assigned by tool. Identifier is in different column in same sheet. The error message has title also. The sheet can be represented as follows ....
View Replies!
View Related
Replace The First Or The Last From String
I need a code which replaces the given letter or sign combination at the first or at the last position of a string. User should be able to chose from which end the replacement should be done, what should be replaced and in which column. Maybe throug a messagebox! For example (1): Column B: [trn][m1][c blue]$ [/c] = Dollar. [/m][m2][c green]Currency of USA[/c][/m][/trn] [trn][m1][c blue]€ [/c] = Euro. [/m][m2][c green]Currency of EU contries[/c][/m][/trn] Result of replacing the first "[/c]" with "" should be: [trn][m1][c blue]$ = Dollar. [/m][m2][c green]Currency of USA[/c][/m][/trn] [trn][m1][c blue]€ = Euro. [/m][m2][c green]Currency of EU contries[/c][/m][/trn] Result of replacing the last "[/c]" with "" should be: [trn][m1][c blue]$ [/c] = Dollar. [/m][m2][c green]Currency of USA[/m][/trn] [trn][m1][c blue]€ [/c] = Euro. [/m][m2][c green]Currency of EU contries[/m][/trn] ......................................
View Replies!
View Related
Compare And Replace ..
I've searched the board for something close to reverse-engineer but no luck. I'm looking for a macro that will: Examine values in Sheet 1, column B Compare them to values in Sheet 2, column A then... wherever there's a match copy the the corresponding cell contents of Sheet 2, column B into the corresponding cell of Sheet 1, column A What I'm trying to do is append the description of a 4-letter code (from a reference Sheet 2) beside where that code appears in Sheet 1. There won't be a row-to-row correspondence in the match, i.e ABCD might be in (Sheet 1)B45 and (Sheet 2)A920...
View Replies!
View Related
Replace Different Text
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
View Replies!
View Related
Find And Replace ‘*’
I am facing a strange problem with Find and Replace function of Excel. I converted a PDF document into a Word document. I copied the tables in the Word document to an Excel document. As the data had many ‘*’ characters, which I did not want, I used Find and Replace function to get rid of them. However, to my surprise, two things were happening. 1.When I tried to replace ‘*’ in a cell with ‘ Construction revenue ********************** ’, it was deleting all the contents of the cell. 2.When I tried to find all the cells with ‘*’ , it was selecting cells even without ‘*’ I tried various things like changing the format, pasting only values..etc, but nothing worked. Finally I had to use the Replace function of VBA to do the task. For further analysis I am attaching my Excel file with that data.
View Replies!
View Related
Replace Value In Formula
i have a string "251423,a" in cell A2. The formula in cell C2=A2. except for in C2 i want the value to read "251423.a" instead of the ",." the string is not always the same length. so really what i need to replace the ",' with a ".' in a formula.
View Replies!
View Related
Replace Old Add-in With New
I have made one project add inn let say test.xla and it is not VBA code password protected. Now I want to stop project and protect my add in. How I can be sure that customer uses later that my passsword protected version. I can hide check code in another password protected add inn and user must use that add inn too if he want use my add inn.
View Replies!
View Related
Macro Replace With New Data
I have two sheets, first sheet contain UPDATE LIST PRODUCT and second sheet contain OLD LIST PRODUCT. I want to insert and replace all data in OLD LIST PRODUCT with UPDATE LIST PRODUCT based on PART NUMBER and TYPE.
View Replies!
View Related
Replace Cells Between Range
I have a list that looks like the list on my sample file. You will see ME28A16 at the top and the bottom of the list. I want to fill the Cells inbetween with that value. In my real file It may start ME or it may start PE Here is the Script that I am using but I can not get it to work: Dim CurrentRow As Long Dim LastRow As Long Dim CurrentText LastRow = Range("A65000").End(xlUp).Row For CurrentRow = 1 To LastRow If InStr(4, Cells(CurrentRow, 4).Value, "M", vbTextCompare) > 0 Or _ InStr(4, Cells(CurrentRow, 4).Value, "P", vbTextCompare) > 0 Then CurrentText = Cells(CurrentRow, 4).Value Else Cells(CurrentRow, 4).Value = CurrentText End If Next CurrentRow
View Replies!
View Related
Replace Array Empties
cell A1 contains a value : " 2.5; 3.68;;;9.87" I have the following code DataArray = Split (Cell(1,1),";") For x= 0 to UBound(DataArray) if DataArray(x)="" then DataArray (x)="-" Next X Do to the fact that these Arrays can get rather large, is there another quicker way to run this code? I had a similar issue with converting text to numbers with using a for loop but was able to use the code range(cell(1,1), cell(20,5)).value=range(cell(1,1), cell(20,5)).value (I'm just trying to make the code run as quickly and efficently as possible)
View Replies!
View Related
Find / Replace Two Different Chars
Assume someone typed the hereunder date ad TEXT. The Formula bar shows: ="01/01/2009" The cell presents: 01/01/2009 I need to remove the equation sign as well the two inverted-commas. I am familiar with the "Text to Column" feature, the use of SUBSTITUTE Function, a short macro and also the Find&Replace is also a good idea but, as far as I understand, it must be run Twice(!) I am looking for a way to use Find&Replace in "One! shot" - meaning, to put the TWO different(!) characters [ the '=' and the " ] in the 'find what' window, leaving the 'replace with' window empty. I know that something similar to that can be done in a "Word" document - but can it be accomplished in Excel?
View Replies!
View Related
|
TRACKER
