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")? blankor 0would exclude these numbers, i.e. would return "nlgahighstreeten"
I have a string of letters in cell column L20 through L3019.
The letters are A, B, C, and R . No other letters.
I wanted to find out if it is possible to remove any duplicates when starting in cell L20 going downward.
For example:
L20 = B L21 = A L22 = A L23 = B L24 = C L25 = C L26 = C L27 = C L28 = B L29 = R L30 = A L31 = C L32 = C L33 = C L34 = R L35 = R L36 = B L37 = B
In the range L20 through L37 all the letters have appeared so my goal is to create a formula where the outcome is BACR. Starting with the first letter B in cell L20
Cell range L20 through L37. Looking for the next letter which would be either A,C, or R. Since we already used the letter B. In this case it's the letter A ABCR
Cell range L20 through L37. Looking for the next letter which is the C or R. In this case it's the letter C CBRA
Finally range L20 through L37. Looking for the letter R which is in cell L29. RACB
Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.
Example:
376 377 421 376AB 376XY 377NC 421GQ 421EF
Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:
I have an option button control on a spreadsheet and I can't seem to remove it from the sheet. I can't right click on it, I can't delete the rows it is in.
When right clicking on the sheet tab, I need to know if it is possible to remove the "Delete" from being an option. My workbook is structured to where if a single sheet is deleted, it screws up all my formulas. I tried to just password protect the workbook, but doing that removes the "Rename" function, which I still need.
I have created a questionnaire with multiple answers in a userform with multipage.
Everything works fine except on about 10 out of my 30 questions, one of the four answers (all opition buttuns) has a fine dotted line around it.
I really have no clue as to where this line comes from. I have tried deleting the option button and replacing it with a new one. This helped for the concerned button but now the dotted line appeared on another answer of the same question! I have done it several times and each time the dotted line simply shifts from one answer to another but never dissapears! The odd thing is that this only concerns 10 questions, the other 20 are fine...
Is there a way to remove the option to open a file as read only when the file is already open by another user? Ideally when a user tries to open a file that is already in use they would get a message to that effect and the only option at that point would be for them to select "notify" when file is available.
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.
I have to manually go through about 9,000 workbooks. In cell E43 of a certain sheet called "list" I have to delete underscores(_) and replace them with a single space. and remove the Rev** after each name
In example: company_name_t45671000_RevA2
Will look like this when I'm done: company name t45671000
Now I've tried to make a Macro that will delete the underscores and the Rev which worked fine except that it replaced the names with the the name that the macro was recored under.
IE: The first sheet I done worked fine when I hit the keyboard shortcut command which was company_name_t45671000_RevA2.
The second workbook sheet of "list" got fixed but had the name of the one I fixed before it: company name t45671000, where it should have been "company name s6743245.
Is there a way around this?
Also sometimes the sheets are protected, is there a way to incorporate "unprotect sheet" when it needs to be unprotected and then after the file has been corrected, re-enable protection again?
The numerical results in column A need to have the last three characters stripped from the cells. I used the =LEFT formula in adjacent cells to return the results but I am looking for a way to run code to remove these three numbers in each cell from row 1 to 8000 in column A.
i want to convert an excel spreadsheet into a text file, keeping the same format, but when i do so, excel puts " " around the characters, which i don't want. Example: please see the 2 attachments.
e.g. when i convert, i don't want the " " around the commas in the text file.
I have a set of data in column a that consists of email addresses. These email addresses all have underscores after them, ie "abc@hotmail.com_______". It will be a different amount of underscores everytime and I don't want underscores to be removed that are actually part of the address. I had been using the find replace function through vba, ie
The macro I have select 2 columns and 2000 rows. I need a VBA code that will loop through each of these 4000 cells and remove all characters (replace them with blanks) that are not a number, a period or a decimal. Characters from other languages like Chinese, Japanese and Russian should also be removed.
I am trying to reformat the following data: 10-10-14-1W5 needs to look like this 100101001401W500. The full description is actually 100/10-10-014-01W500 the desired result is without the slashes and dashes. I have tried to add a custom cell format of 00-00-000-00L000. but it will not apply to the existing data.
I've come across multiple times where I have to do this same sort of task, and I don't think I am doing it the most efficient way.
What I need to do is take a variable which holds a string and remove the last 9 characters from it. Don't need to know what the last 9 characters are, all I care about is knowing what the other characters are in the string. I know this can be done through thingslike susbstrings, but I don't think VBA has a substring function.
i deal with column that has client initials, date of birth and gender, in this format t-b-23/05/72-f however i want to remove the initials and gender(i.e. f or m on the left) and - so that only date of birth remains in same column.
I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?
I have a column of data, (10,000 entries), a list of file names basically, e.g:
Mortality Project Executive Summary.pdf RPP - AA rate and swaps.xls Commodities.PPT presentation.ppt Meeting Preparation 20090302.docx
Anywho the point is I want to remove the file extensions (and of course the "."s just before), but some obviously have 4 character extensions, some 3, some 2 etc... Some documents also have "."s in the file name that I do not want to remove, basically just working from the right keep removing until the first "." is removed. So the final list wants to be:
Mortality Project Executive Summary RPP - AA rate and swaps Commodities.PPT presentation Meeting Preparation 20090302
I know I should VBA it, but I want it all in the one spreadsheet and that's a bit above me. The document has about 30 other columns.
my values within column A are separated by a dash. I'd like column B to continuously copy column A with the exception of the characters after the dash. example:
I have several hundred columns of data in this format: |#########|. I need to remove the non-numeric characters on the edges of the numbers so I can manipulate them. Is there an easy way to do this? I do not want to manually remove all of the characters.
