I'm trying to do a quick find and replace across a range of cells to remove Chr(10) and Chr(13) characters. For some strange reason, some of the cells are being good and duly allow the removal of said characters and some are not - they stubbornly refuse to remove with no error message. The ones that are not allow removal if I copy the individual cell to a new sheet.
Sub removeCharNew()
'Dim x As Integer
Cells(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Cells.Replace Chr(10), ""
Cells.Replace Chr(13), ""
End Sub
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
I have some records that include greek addresses. The greek alphabet contains letters that are common to the english one (A,B,E,Z,H,I,K,M,N,O,P,T,Y,X) and records with mixed characters will cause me problem. What can I do to find and replace any of those letters with the greek version of the letter.
example:
for a record:
T..5123 (this is the Greek abbreviation for P.O.BOX 5123)
I'm looking for a function to replace the first letter T (wether is Greek or English) with the Greek version of it
example 2:
KYMH 6 (kimis is the street, 6 the number)
I should replace English (first four ) letters with the Greek version...
example 3:
BAPITOY 14 (valaoritou is the street, 14 the number) ...........
A range of cells contain characters that i want to replace with spaces. The problem is that the length of the string differs and also the characters differ.
For example, below a list of cell values:
GR GRKR GRKRKR GRKRK1 GRON GRONBL
As a result for the first value i want 2 spaces, for the second one 4 spaces etc.
Is it possible to do this with a formula? I don't want to use VBA for this if possible...
Is it possible to display a dialog box or msgbox that doesnt have an OK button ?
i.e I want a message that comes up on the screen that says "Links Updating...Please Wait" which then automatically changes to "Links Sucessfully Updated" on completion...I dont want the macro to be interrupted by the msgbox/dialog...
I download several sets of data from an internet site. The only way I can do this is to copy and paste into Excel. The problem is, that all of the cells with numbers in them show up as text, and when I press F2, I find that there is a blank space at the end of each number.
Find and replace, searching for a blank space does not work - Excel "cannot find any data to replace". That function does work on cells in which I have created my own space at the end of a number, but not on these numbers. That leads me to believe that that "blank space" is actually some other invisible character that I need to be searching for. I've attached a bit of my data - maybe somebody else can identify my phantom character.
To get around this, I have been using the formula =(left(A1,len(A1)-1), and that works, it's just that it is a pain to create another table of 20-some columns with this formula, then copy those to values, then for each column convert text to columns to change the text to values, then search and replace for #value! where the original cells were blank.
I have a worksheet that I produced from optical character recognition, and there are a lot of funky characters that I need to get rid of. One is a line break or carraige return (I assume it's the same character that I could insert by entering ALT+ENTER). How do I search for this special character to replace it? How do I identify what that invisible character is?
I have very large text data which contains accented characters (E.g., "ÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿˆµ" etc.) within words.
Since I have several data sheets, some of which having 500000+ rows, Find and Replace and similar options are not viable.
I wish to replace each of these characters with regular English characters in all sheets. If that is difficult, these must at least be removed from the whole text. note that I need to preserve regular characters that are commonly used, particularly the hyphen (-) and underscore (_).
I am looking a macro which should perform below activities
I have a lists of suppliers which needs to cleansed....
1.replace all the special characters with a space
2.replace Corporation with Corp Incorporation with Inc Limited with Ltd Limited partnership/ltd partnership with LP Company with Co ASSC/ASSOC with ASSOCIATION/ASSOCIATES (SEARCH SUPPLIER IN GOOGLE FOR THE CORRECT ONE) MGNT with MANAGEMENT SVCS with SERVICES DEPT with DEPARTMENT Unltd with UNLIMITED Ctr with CENTER UNIV with UNIVERSITY
3. Remove any text, Special characters and numbers after INC,CO, LTD, CORP,LLP, LLC
The macro should when I select any particular column... not restricted to column A
I have a long list of items in one column, with parameters that are all split by an underscore. However, in some instances, some of the text that is separated by an underscore needs to be joined together.
So for instance I'll have;
This_is_a_very_good_thing This_is_a_perfect_thing
Where I want the result to be that "good_thing" becomes "good-thing" and "perfect_thing" becomes "perfect-thing".
How can I get this done with a Macro, instead of constantly doing a Vlookup to change this instances.
I basically want to find and replace certain characters in a formula, but the formula is quite intricate so I will give some background to the problem first:
I have a complex spread sheet which is used to check if a data array matches another data array (both data arrays are different sizes). One array has a set of parameters for a variable and the other array has the variables and the actual values for each variable. I have a section with the following formulas to check if the actual value (C121&D121) matches the parameters ($A$9:$A$108, $B$9:$B$108) & ($C$9:$C$108, $D$9:$D$108)
I have the following formula in cell AB121: =(C121=INDEX($B$9:$B$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
In cell AC121: =(D121=INDEX($D$9:$D$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
And have similar formulas from AD121 to AY121. I mention this to point out that excel regards this as an inconsistent formula, I haven't copied and pasted it over, I have had to re-enter it in each cell adjusting the parameter sections.
The problem I have is that currently this is set up to check through a data set and wrap around every 100 rows ('100' Entered in $D$1) but I want to adjust the formula when there is a different amount of rows to check. So basically the only way I can think to be able to do this is to have a formula or something in VBA (which I have very low level experience with) to be able to go into the formula and change certain characters within the formula. For example, if I wanted to check 95 rows instead of 100, it would need to have 95 in $D$1, which I enter in manually, but then how to go in and replace (101:101)-INT(ROW(100:100) with (96:96)-INT(ROW(95:95) in 2 sections in the 1 formula then do the same with the inconsistent formula from AC121:AY121?
I have a conversion issue. I have a sheet that is In Japanese (Shift-JIS) and some characters are replaced by boxes (squares), I have tried to record a macro to combat this, ie copy the symbols (squares) and replacing the squares with a corresponding letter. It works when I record the macro, but when I run the macro again it replaces everything with the letter I choose.
Sub replacing() Columns("A:A").Select Selection.Replace What:="??", Replacement:="RR", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub
It seems that the macro is not able to record the squares and replace them with R. see attached xlsheet.
Working on a macro to replace a list of about 20 specific "Special" Characters in excel, and have ran into 6 that will not work.. Following are the characters: ā, č, ć, ř, ş, ż
QUESTION: How do I check to see if a cell contains either "" or "/" and if it does then replace it with "-"? THE REASON: I have a form which needs to be saved in a particular directory with the name "Form 1 - client name - matter name". To do this I have put =("G:UsersCComplianceFile OpeningForm 1 - "&B7&" - "&D42) in Cell A1 and then in VBA added the following macro to a button which saves the file
This works ok BUT, if the user puts "/" or "" in either B7 or D42, it won't save and throws up an error because you can't use those signs when saving a file.
I have two sheets in my workbook: One containing names with French and other European characters, the other containing a list of ISO entities for these characters, like these:
#224 #225 #226 #227 #228 #229 #230
I need to replace these characters in Sheet 1 with the equivalent ISO entity from Sheet2 and print the value into the same cell in Sheet1.
I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?
I'm having difficulty creating a nested "if" or range formula that basically only reads the first two characters of the data in a given cell. See attached spreadsheet.
In column "A" I have listed the product ID. If the product ID has the first 2 characters 'DV' then it should yield the result of Dryer. The main issue is creating a formula that only picks up on the first two characters.
I need to do a sumif range A:A does not contain 484- as the first four characters of the cell. The 484- is not the only part of the cell containing that text. How would I do this? Ex:
A 16-1700.049820-F704264.59484-0068400F704264.59484-0101928264.5916-525129.00
Basically, looking at the range E6:L19 I need to ensure that any data that is entered into any of these cells ends in either H or W, and if it doesn't flags up a warning message (which I am hoping to link in with the Worksheet_SelectionChange event)
I'm managing it for one cell, but not a whole range.
I have some code that sets named ranges based on sheet names defined by the user.
Issue is, sheet names allow characters that range names do no (i.e. #, - etc).
Is there a relatively simple way to clean a sheet name to allow it to be used as a range name.
Code example below. I am using Replace, but unsure how many characters are invalid. Unfortunately I am referencing a third party workbook, so no control over sheet names.
Sub test()
Dim sh As Worksheet, shnm As String, wb As Workbook Set wb = ThisWorkbook
I'm trying to figure out why i'm getting this error: "Object variable or With block variable not set" for this
Sub Macro3() Dim cel As Range Dim str1 As String Dim SearchThing As Range Set SearchThing = ActiveSheet.Range("I34") str1 = Left(SearchThing.Value, Len(SearchThing) - 4) cel.Value = str1 End Sub
I have a column of data; for each line of data I have something like ABCDEEast Anglia, ABCDFFarnborough. The text at the start is standard and all cases of East Anglia will have ABCDE prior to the East Anglia. Is there an easy way [aside from replacing] to loop through 1000 data points and replace the long method with a shorter concise version (i.e. East Anglia only). I have attached what I mean
I'm trying to write some code that will make excel examine a particular range, determine if there is text in each cell, and if there is replace it with different text.
