Remove Diacritics

Aug 7, 2008

I need to conver a string that conatins diacritics "Stanišiæ" (note the s and the c) into a plain text version, = "Stanisic"

ADVERTISEMENT

Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers

Aug 8, 2009

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´).

Option Explicit

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

Two requests:

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"

View 5 Replies View Related

VBA To Remove Any Value Of 0?

Apr 28, 2011

I need a VBA that will look through a spreadsheet in a specific range and clear any cell values of 0. The range would be Columns E,F,G,H,I,J,K,L all the way up to row 4000.

View 6 Replies View Related

Remove Add-in

Aug 13, 2008

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?

View 5 Replies View Related

Remove Duplicate

Apr 9, 2014

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.

Attached File : remove duplicate.xlsx‎

View 2 Replies View Related

Add, But Not Remove Or Edit

May 27, 2008

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?

View 9 Replies View Related

Remove The Apostrophe

Nov 6, 2008

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.

View 3 Replies View Related

Remove Those 0 Values, #Num!

Jan 10, 2009

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.

View 3 Replies View Related

Remove $'s From Formula's

Jan 16, 2009

I have hundreds of formula which are linked to multiple spreadsheets. They all have $ signs in which I would like to remove. Is there a quicker way than editing each formula?

View 5 Replies View Related

How To Remove A Macro?

Jan 29, 2009

I done running a macro but I want to convert back before the macro is run. How can I do it?

View 6 Replies View Related

Remove The Spaces...

Feb 26, 2009

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?

View 5 Replies View Related

Remove The Sheet

May 21, 2009

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.

View 9 Replies View Related

Remove Add-In In 2007

May 22, 2009

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.

View 4 Replies View Related

Cannot Remove A Combo Box

Jun 4, 2009

I added a combo box to a worksheet and closed and saved it. But I don't want it amymore. I cannot delete it. I try right clicking it but nothing. The worksheet,(workbook) isn't protected.

View 2 Replies View Related

Remove Everything To The Left Of...

Sep 20, 2009

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

View 4 Replies View Related

How To Remove Apostrophe

Dec 18, 2012

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...

View 3 Replies View Related

How To Remove Repeated Value

Sep 4, 2013

Is there a quick command that I can use to take

math
math
math
math
art
art
art
art
art
music
music
music
music

into

math
art
music

View 6 Replies View Related

How To Remove Duplicates

Sep 25, 2013

I have an excel document that looks like this: Capture.JPG

I need it to look like this: Capture2.JPG

As you can see, the batch #, document #, amount, debit and credit all duplicate based on the number of debits that appear for each document #.

View 3 Replies View Related

Remove All Zero's From Cells That Contain Zero Only

Feb 23, 2014

Wanting to remove all zero's from cells that contain zero only.... I don't want to hide them I need to remove them....

have tried several options and can't seem to find the 'right' one....

Find and replace is not and option; unchecking display zero not an option and whilst I can sort each column and manually delete the zero feel there should be a better way...

View 4 Replies View Related

Remove Many Rows At Once

Apr 4, 2014

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.

View 5 Replies View Related

Remove Commas On CSV Through VBA?

Jul 25, 2014

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?

View 8 Replies View Related

Remove The Toolbars

Mar 27, 2008

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:

View 14 Replies View Related

Remove Value From Listbox

Apr 18, 2009

On the form, I have a button to add files to a listbox (the box is bound to a worksheet). how to program a button to remove a line from the listbox and the worksheet? here is the code

View 3 Replies View Related

Remove Entries In Col A That Appear In Col C

Jun 10, 2009

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.

View 14 Replies View Related

Remove Certain Duplicates ....

Jun 29, 2009

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.

View 8 Replies View Related

How To Remove All Shapes

Nov 17, 2009

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

View 10 Replies View Related

Remove All Connections?

Feb 9, 2010

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.

View 3 Replies View Related

Remove All The Dashes

Jun 14, 2006

Have a column containing data which may have 1-3 dashes locates within
each cell value.
Such as:
125-457A
158-857-1116
124184A-1
125-8745-22D9
458-2145-002-7

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:

125457A
1588571116
124184A-1
125874522D9
4582145002-7

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.

View 9 Replies View Related

Remove #n/a From My Cell

Jan 9, 2009

i have the following formula: =REPT(Estimator!D8,(Estimator!E8="Yes")). when it is not in use or false it displays #N/A.

View 5 Replies View Related

Remove N/a From The Cell

Jan 13, 2009

who to make cell cell display a balnk/nothing.

Im using the following function

=VLOOKUP(C15,Estimator!C8:D17,2,FALSE)

View 8 Replies View Related

Remove A Password

Feb 2, 2010

I have givien a password to a workbook through the save as meathod. How do I remove this password so anybody can just select the workbook to open it?

View 2 Replies View Related

Remove Last Two Words

Feb 23, 2012

Formula to remove the words " Sales Rep"

this is the original word:
Atlanta Sales Rep

I want the formula to remove " Sales Rep"

View 4 Replies View Related

Remove 1st Duplicate Only?

Aug 9, 2013

Is there any way of Removing the first duplicate in a list only? I am writing some vba to automate a month end process and wonder if there is a way to achieve this? (excels remove duplicates function keeps the first, and removes everything else). The data is in column C.

View 2 Replies View Related

Remove Set Of Rows

Oct 31, 2013

I'm puzzled on coming up with a way to delete a set of rows in Excel. I have two columns like below and would like to remove the rows with the later year. Is there a formula or vba I could use to run on 5000 records?

Current table:

Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12

[Code]...

Desired table: [this would exclude rows where record source = MCL-INV-SUR09

Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12

[Code]...

View 4 Replies View Related

Vba To Remove Zeros

Nov 6, 2007

i have numbers like:

00012334
00122434
00000344
00003432

i need:

12334
122434
344
3432

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.

View 9 Replies View Related

Remove The Spaces

Dec 11, 2007

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.

Ex:

*7 Dec 2007*DEPOSIT*3,917.63
*7 Dec 2007*DEPOSIT*1,890.58

View 9 Replies View Related

Remove Duplicates

Apr 24, 2008

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.

View 9 Replies View Related

Remove Certain Characters From A Row

Jun 5, 2008

let's say row 2 has data that looks like

apple (kg), apple (g), orange (kg), orange (g)

it is possible to remove the (kg) and (g) tags so that it'll become
apple, apple, orange, orange

using VB code?

View 9 Replies View Related

Macro: Remove The Row

Dec 30, 2008

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.

Sub RemovePersonnel()
With Sheets("DOR")
.Unprotect Password:="techedit"
Rows(56).Delete
.Protect Password:="techedit"
End With
End Sub

View 9 Replies View Related

Remove Data

Jul 20, 2009

I Want to Delete Data.

Filter I Column And Delted "N" ....

View 9 Replies View Related

Remove Values

Aug 14, 2009

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....

View 9 Replies View Related

ADVERTISEMENT