Replace 2 Characters Signs In Cell With Another
Mar 15, 2008
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
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
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.
View 3 Replies
ADVERTISEMENT
Sep 3, 2013
Column H contains alphanumeric data (codes).
I need a macro that renames all cells in column H starting with the letter "B" by replacing B with "Temp" and thereafter cuts the last two digits of the code. B224501 would thus be Temp2245.
View 2 Replies
View Related
Oct 14, 2009
I need help with a formula (no VBA) that will find all the semi-colons ";" and pound signs "#" in a cell and replace them with this "<br> - ".
Example of Cell A1: ;#Red;#Green;#Blue;#
I started with this:
=REPLACE(MultChoice,SEARCH(";#",MultChoice),1,"<br> - ")
but it only replaces the first character.
View 12 Replies
View Related
Jul 2, 2013
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?
View 4 Replies
View Related
Feb 11, 2009
Replace characters in a string. I have this macro:
View 2 Replies
View Related
Jan 31, 2010
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) ...........
View 3 Replies
View Related
Dec 10, 2011
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...
View 5 Replies
View Related
Nov 28, 2006
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
View 2 Replies
View Related
Oct 4, 2007
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...
View 5 Replies
View Related
Aug 28, 2009
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.
View 9 Replies
View Related
Oct 14, 2009
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?
View 2 Replies
View Related
Aug 15, 2013
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 (_).
View 3 Replies
View Related
Jul 8, 2014
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
View 1 Replies
View Related
Nov 29, 2009
I have been trying to do the following but have not been successful. thanks in advance for helping!
- i have lots of text strings on one worksheet
- some text strings have <randomstring>, some even have multiple instances of <randomstring>, but others don't have a <randomstring> at all
- what i want is remove anything within "<" and ">", including the operators "<" and ">"
i have done this so far, with simple text functions, have each of the following lines in separate columns (text string column is "C"): .....
View 10 Replies
View Related
Sep 9, 2013
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.
View 3 Replies
View Related
Sep 28, 2013
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?
View 3 Replies
View Related
Dec 29, 2009
I have about 40,000 lines in Column A that have text like so...
13HMPS32TRE600001OP000000601
13L9KUPSOTE600001OP000000601
I need a formula to change all TR and OT to characters QT. I did a find and replace but its changing other columns of text that I don't need changed.
View 9 Replies
View Related
Sep 21, 2007
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.
View 2 Replies
View Related
Jan 15, 2008
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: ā, č, ć, ř, ş, ż
ActiveSheet.Select
Selection.Replace What:="", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
View 5 Replies
View Related
Aug 11, 2014
I have list of email ids and some of which contains accented characters.I need to replace these accented characters with regular character.
Example:
rian.pieda@intt.com.cn
ario.sancez@acca.com
View 2 Replies
View Related
Jul 18, 2014
i'm looking for macro for find and replace characters in a locked sheet, if a cell contains for example Monday (First day of week)
I want with a command or macro to change the (First day of week) in (Top of the Week) like this Monday (Top of the Week)
View 9 Replies
View Related
Mar 27, 2007
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.
View 3 Replies
View Related
Mar 18, 2009
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 2 Replies
View Related
May 18, 2009
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 9 Replies
View Related
Dec 2, 2008
I am looking to create a "Retractable Menu with the + and - signs". If you don't know what I mean, go to excel help and click on the contents tab - that is the menu system I am talking about.
View 2 Replies
View Related
May 15, 2012
I have created a macro that searches my drives for files, lists them in excel and turns them into hyperlinks. My problem is that a majority of the names use # signs in the file name. What is the best way of going around this problem without changing the file name complete. There is approximately 100,000 different files in different locations that would need to be alters so I think it would be easier to just make the name link work with the # sign.
View 1 Replies
View Related
Jul 6, 2012
I have this autosum macro it does its job very well. Problem is that it returns autosum with absolute cell references ($ signs around cell numbers). This way I cannot copy paste formula around same row but different columns. I would rather have to copy paste code for each column and change declarations.
How to edit this code so that variable cell references are returned in the result.
Code:
Dim cel1 As String, cel2 As String
cel1 = ActiveCell.Offset(-2, 0).End(xlUp).Address
cel2 = ActiveCell.Offset(-1).Address
ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"
View 3 Replies
View Related
Feb 8, 2007
I need a macro that will change a number's sign. To go from neg. to pos. or pos to neg. I need this macro to execute this on all selected cells. So, for example, if I select A1:G35 and execute this macro via button or short cut, all those selected cells with numbers will flip signs.
View 9 Replies
View Related
Jun 17, 2006
Could anyone guide me how to express symbols & signs in VBA , LIKE ....
View 6 Replies
View Related
Jun 30, 2006
I need a macro or something else that could delete the dollar signs from all the formulas on my worksheet. in row 4, columns V-FR, each cell shows a value that is calculated by an underlying formula. the formula for each cell is different. is there a way to scan all the formulas and delete the $ signs??
View 2 Replies
View Related
