I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Private Function RemoveCharacters(InString As String) As String Dim intLoopCounter As Integer Dim intStringLength As Integer Dim intASCIIVal As Integer intStringLength = Len(InString) InString = LCase(InString) For intLoopCounter = 1 To intStringLength intASCIIVal = Asc(Mid(InString, intLoopCounter, 1)) If intASCIIVal >= 97 And intASCIIVal <= 122 Then RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1) End If Next intLoopCounter End Function
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
I am trying to find the solution for the problem I faced after downloading the "Color Filter" utility from this forum (Handle Duplicates & Color Filter)
I really do not understand why I was banned. I've downloaded utility from THIS forum and have kindly asked help on how to remove it because it does not meet my needs. I am not an expert in VBA and expected that somebody could help me. (It looks like the person providing the utility in the above indicated post shared the password as everybody is thanking him but I don't see where it is!)
I just want to remove the utility from my PC!!! And, if I can do this without bloody password than I do not want it! But, can somebody just explain me if there is another way to remove this from my PC or I have to reinstall EXCEL ?
I must say that I find it really increadible for beeing banned twice for no reason. Isn't it worse to place the password protected utility so that the one dowloading it cannot remove it anymore?
I attached a file in which column A is dr_cr and E id INST_NO and column G is INST_AMT. This file like a bank statement. in which one instrument(cheque) present and i denote it c(credit) in column A. but if cheque credit then d(debit) means that this cheque present and dishonour. but some time one cheque credit and then debit and then credit. it means that we have to remove previous credit and debit entries. in this attached file you found this type of entries. i want to remove this type of entries. i further explain.
1. if one instrument have one credit and one debit its ok.
2. if one instrument two credit and one debit then remove one credit and one debit where instrument no and amount and drawee bank must be same.
3. if one instrument have two credit and two debit we have two remove one one debit and one credit.
4. if one instrument have three credit and two debit then we have to remove two credit and two debit so one credit left.
Is there a way to setup a spreadsheet so data that has been entered cannot ne deleted or edited, and only the last field in the column can be accessible? So if I have info entered in a1, a2, a3, b1, b2, b3, no one can edit or delete them, and can only add to a4 and b4? Will I need to write a macro for this or is it something I can do with certain settings?
An apostrophe has appeared at the beginning of the words and/or numbers in the cells. I have tried using the "Replace" feature to remove these apostrophes and it won't work. I need to remove the apostrophes so I can upload our inventory, as the SKU must be pulled exactly.
I have a cel that has about 100,000 phone numbers and at the end of some and before sum, there are spaces added to end. is there a formula, or a way to take everything away except for the 10 digit number?
I am looking to remove the daily expenses summary sheet and continue to use the expenses detail sheet, but on the main weekly business sheet where you enter the financials, I need the expenses line to contain expenses for each day.
It is my intention to simply enter monday in the day colum in the expenses detail sheet and in the weekly business sheet it would pick this up.
So the formula I had was =ifsum(!expenses detail a2:a40, !expenses detail monday, e2:e4) This formula is giving me a name error.
Am I using the wrong formula. Basically I want this type of formula so under the expenses row for mon-sun it will add this up.
I've been sent a document by a client which has come with an add-in which has automatically installed itself into the Ribbon. It's on the Add-In tab (obviously!) and the Command Group is named 'Custom Toolbars'. What's annoying is once I close the document, the Add-In stays there and I have a redundant tab with an irrelevant Command Group .
Now, I've done a bit of searching and how found a number of websites that give instructions on add-ins. However, the add-in that has come with the client's document isn't on the list and hence I can't remove it. I need to do to get rid of this? I've contacted the client, but they're suspiciously quiet about the whole thing.
I need a formula that can remove all characters to the right of "]" for example, if a1 = abcdefg]1234, I want b1 to return the value: 1234. I tried =RIGHT(a1,FIND("]",a1)-1), but that didn't really work. It returned: fg]1234
I can't remove the apostrophe mark from the cells. There are cells appearing to be blank but they all contain a single apostroph (like this ' ) if I click on them. Whenever I tried to replace them excel said cannot find a match...
I am using the following code to remove an entire row if a specific cell in that row contains "remove":
iLastRow = Cells(Rows.Count, "D").End(xlUp).Row For i = iLastRow To 1 Step -1 If Left(Cells(i, "D").Value, 4) = "remo" Then Rows(i).Delete End If Next i
However, from what I can see, it starts from the bottom and look upward, removing each line as it finds it. The problem is, there could be upward of 500 lines that need to be deleted. This coding looks and removes lines one at a time and can take a while. Is there any way I can get this done faster?
During the previous steps of my macro, I sort the column (Column D) that has the "remove" in it, so all of the "remove"s are adjacent to each other in Column D of my file.
I copied my data and used the paste value function, pasted it onto another excel sheet and then saved as CSV. Ideally, there should be no formatting on the cells. However, column J threw some commas for which i am unable to understand.
All i want to do is put a control in place via VBA to remove all commas from the sheet.
Is there a VBA code that can perform this function?
I have a slight problem with some code I am using. I have a user form where I force the users to enable Macros using the hidden sheets trick. Once the user enables the macros all of the toolbars dissapear from excel. The code I use to do this is below:
Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C.
Hope this all makes sense. Please feel free to ask if it is not clear.
Is it possible to scan through column A for duplicates if found delete the row that has not got any data in either column D E or F? If Duplicates are found and neither have any data in D E or F Delete all but one of the duplicates.