I need to conver a string that conatins diacritics "Stanišiæ" (note the s and the c) into a plain text version, = "Stanisic"
View Complete Thread with Replies
Related Forum Messages:
Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers
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)
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?
Remove The Word
how to automatically remove the word " Total" that Excel puts beside what you are totalling while using a subtotal?
For example, I am totaling a value of orders for one company and Excel puts "COMPANY NAME Total". Or is there a way to use the Right function to delete the 5 last letters of text in a cell? I've tried using the right function but can't seem to get it to work to delete text...
Remove Entries In Col A That Appear In Col C
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.
Remove Certain Duplicates ....
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.
I have a sheet that looks like this.
JOE D.02082008/28/199 H PAnnual Increase06/05/2006
JOE D.02082008/28/1995 H PReclassification06/11/2007
JOE D.02082008/28/1995 H PReclassification02/11/2008
The name is in Column F the employee Number is the 020820 and we want to only have the latest date in the last column.
So out of this data above. The bottom one with the 2/11/2008 is the one we want to keep.
And delete those other 2.
The next group has duplicate names 10 of them. But only want to keep the most recent.
Macro: Remove The Row
I have the following macro that deletes a row. i also have a macro the inserts a row. my end users can basically insert of delete rows on a standard entery form, as they need. my question is about the deletion macro code. Currently the user can click on the "delete a row" button on the sheet and remove row after row after row. If they get overzealous they can actually begin to delete a row which they need for other data. I am wondering if i can make it so i have a permenant number of rows, say three, and if after that the button is pressed to delete another row, nothing happens.
Remove The Sheet
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.
Remove The Spaces
I am trying to remove spaces in front of a number (currently formatted as text)
I have tried "Text to Columns", "Trim", and other suggestions in previously threads.
I have copied bank statement amounts from an e-mail and the $ amounts have one Space in front of them. When I use the Trim function, and then a paste special, I still cannot get rid of the space, and so cannot add up the amounts in this column.
*7 Dec 2007*DEPOSIT*3,917.63
*7 Dec 2007*DEPOSIT*1,890.58
Remove The First 4 Digits
1. Remove the first 4 digits from each "Appeal ID"
2. Insert a new column (first column) called "Chapter"
3. Run a v-lookup down the new column against a file that is stored on my desktop. The v-lookup will cross check the Appeal ID against the file to identify the Chapter
4. Sort the data alphabetically by Chapter
5. Create seperate Excel files for each Chapter ...
Remove The Percentages
I have data that I import from another source into excel and it pulls over like this
I want to remove the % since these aren't actually percentages, the report we pull from has them listed incorrectly and cannot be changed since it is software driven, not excel driven. The problem is simply remove the % changes the number to 38.66 instead of leaving it at 3866.
Remove All The Dashes
Have a column containing data which may have 1-3 dashes locates within
each cell value.
Would like to remove all the dashes "unless" the last dash to the right
is followed by a just a single digit. The results would be as follows:
On the example data, it kept 2 of the dashes in the cell values,
because the were only one digit away from the extreme right, but
removed all others.
Remove The Toolbars
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:
Remove Those 0 Values, #Num!
There are 2 sheets in the file attached.
-Data : Contain all the acc number and balance.
-Working : a sheet for my working to derive what I want . A string contain all the Acc Number
Bascially first I will paste and copy the acc number and balance from an external source to Data, then I wan to create a string of all the acc. This is done by the working from my Working Sheet.
In my Working Sheet, first It will take the value from the Data Sheet. Since the number of Acc / Balance may differ, I wan to know how to remove the 0 and #Num! for those null values.
Following that, I create a column sorting the all number from smallest to biggest, following by next column forming up the string. I do not want the same Acc to repeat in the string.
Remove All Connections?
I've got a little program which uses webqueries to import data from anywhere between10 and 200 web pages. It's working pretty well, but I need a way to clear out the residual connections via the code.
The part of the macro which imports the data was simply recorded and very slightly modified. I've included it below... but what I'd like to do once the data has been imported is delete the connection.
How To Remove All Shapes
i have an excel file for using my manufacture planning. i dont know why but there was created too many TextBoxes(but its shapes)
I'm giving my excel file , you can see what i'm talking about at G column and 193. field
if you can solve this problem, i will be pleasure to you
Add, But Not Remove Or Edit
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?
Vba To Remove Zeros
i have numbers like:
i cant use formulas because its in text and for some reason if i try to use a formula the formula shows but not the answer.
vba would be prefered.
Sheet1 ABCDEFG1DocS.DE.DDESCFCPOSOLD2202204/27/200904/26/2010 DG121213202204/27/200904/26/2010Laptop123DG121214202204/27/200904/26/2010Laptop123DG121215202204/27/200904/26/2010Laptop123DG121216202204/27/200904/26/2010Laptop123DG121217202305/04/200905/04/2009 DG121218202305/04/200905/04/2009Monitor186DG121219202305/04/200905/04/2009Monitor186DG1212110202404/27/200904/26/2010 AG12311202404/27/200904/26/2010Keyboard135AG12312202404/27/200904/26/2010Keyboard135AG12313202404/27/200904/26/2010Keyboard135AG12314202404/27/200904/26/2010Keyboard135AG123 Excel tables to the web >> Excel Jeanie HTML 4
ANSWER sheet 2
Sheet2 ABCDEFG1DocS.DE.DDESCFCPOSOLD2202204/27/200904/26/2010Laptop123DG121213202305/04/200905/04/2009Monitor186DG121214202404/27/200904/26/2010Keyboard135AG123 Excel tables to the web >> Excel Jeanie HTML 4
I am looking for VBA CODE....
How To Remove = Sign
looking for a formula to remove an "= sign" at the very beginning. hence if "=Jordan" then want to change it to "Jordan" in cell T3 only.
but wait, the resulting cell T3 "=Jordan" already has a formula inside it =(U3&V3&W3&X3&Y3)
How to enter formula for removing "= sign" and not disturbing the existing formula ....
Remove The Spaces...
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?
Remove The Apostrophe
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.
About To Remove #VALUE! Error
I Want to remove "#VALUE!" error but I do not want to remove "-" sign. I want "-" sign to be treated as "0".
I also cannot use "sum" formula due to some reason & the value can be changed.
Can anyone suggest a formula that will take a number (a variable amount of placeholders), and remove any trailing zeros.
eg. 6000 = 6, , 23 = 23, 230 = 23, 2300 = 23, 23000 = 23
Also looking for a similar forumla that will strip off any numeric characters from a text string of variable length.
eg ACA2343 = ACA, GNVC23 = GNVC, DAL12 = DAL, CGHJ = CGHJ, CGHJ5002 = CGHJ
Remove Add-In In 2007
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.
Add / Remove Userform
I've created this simple form to add and remove data in a combobox
in a userform and i want to add and remove items
note: I've tried to do that but the new added items does't appear
next time i run my workbook
May I know how to remove character like
1) full stop
5) Left and Right Slash
Add Lookup Via VBA & Remove #N/A
I have two workbooks. One is a database (funding.xls) with many columns - the unique identifier in column C (deal number). The other is a workbook (operational report.xls) for which a worksheet ("Not Yet Finalised Deals") is emailed to me daily and lists the unique identifier (deal number) in column C and a code assigned to that deal number in column G. Sometimes there may be only one or two deals on this spreadsheet, other times up to twenty. What I want is a macro which can be run off the funding.xls database (preferably - although the code below runs off operational report.xls) which inputs the Code from Column G of operational report.xls into column AL of the funding.xls database if the deal number is the same. I have written the following, but it kills Excel. I presume this is because it loops for so long, but doubtless you will find many errors/overkill of references in it
Private Sub ISIN_Click()
'Updates the data sheet for the ISIN codes given
Answer = MsgBox("Is funding.xls Open for WRITE access?", _
If Answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
Dim Row_No As Integer
Dim Counter As Integer
Dim keep_going As Boolean
Dim Ref_No As Variant
Dim ISIN As Variant
Row_No = 2
Counter = 2
keep_going = True..................
Remove Characters From Right Of String
I'm trying to remove everything after a specific character in a string.
I.e. change a website address to the hostname
I'm using this formula, which strips the http:// and the www., but does not replace the characters after the first remaining "/" as the wildcard is not recognized.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""), A2,"http://",""), A2, "/*", "")
VBA Code To Remove Duplicates
Private Sub Dupe_Remover()
'29/06/2005 by nhunter
Application. ScreenUpdating = False
Dim R1 As Range
Dim drow As Integer
Dim lastitem As String
Set R1 = ActiveCell
If Trim(ActiveCell) = "" Then
Hey guys, I'm from a Laptop Repair Company. Sorry to barge in here and ask for help randomly, but any suggestions would be greatly greatly appreciated. I post mostly on Actionscript forums to help others using AS3.0, so if you have a question there I could definitely help you lol
I have 2 .xls documents with customer contact information. One has a list of old contacts I need to remove which is about 3k this list contains only email addresses. The other contains the full list of Customers this list is about 11k this contains everything from first/last name to billing and email addresses.
My goal is to double check to see which addresses are just typos and which are non-working.
I will need to go through the list line by line but better to only go through 3000 than 11000. So, I need to somehow remove all of the good addresses from the full list using the bad address list which is only 3k long.
Any suggestions on where to start?
Remove Text Header
I have two txt file where the report is being generated but importing it in excel and using text to column distorts the data plus it has a header which repeats on every page... how can delete those header and tidy the sheet up. I have attached the txt header example.. Any help on this.
Remove Trailing Commas
Removing Trailing Commas
I have a list (general ledger) GL codes with trailing commas
When I try to use “replace” and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.
The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes
Is there a function that removes trailing commas, or commas that are not followed by numbers?
Remove Duplicate Addresses
I have 2 issues i am trying to work through, i have a spreadsheet with 3 data sheet tabs, the first issue i have is in Data sheet 1 I have a lot of duplicate addresses, I was wondering if there is a way to filter out the duplicate addresses so that only one of each address is showing.
Issue 2 that i have is a lot more complicated, In data sheet 1 i have a list of medical providers that reimburst at 110% or more, each address on that list needs to get 1 letter mailed to them, but i have to keep track of how many are going to each region, i.e. Columbus, OH region, Cincinnati, OH Region, Toledo, OH Region, and Cleveland, OH region. So what i want to do is create a 3rd data sheet that keeps track of total letters sent by region, but was wondering if it was possible for this info to be automatically transfered from data sheet 1 to data sheet 3. I don't need all the information in data sheet 1 to tranfer to data sheet 3. All i need is for data sheet 3 to read off the City field in data sheet 1, and calculate 1 letter sent to that region.
I dont even know if this is possible, but it is way beyond my realm of excel knowledge which isn't much.
Any help would be appreciated. I am trying to attach the spreadsheet, however it isn't allowing me to attach it, i keep getting a database error, when i upload it. It is under the max size limit too. Not sure what's going on.