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.
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 have got a cell or cells with certain number of Characters (Alphabetical or Numeric or Alphanumeric). I would like to remove the excess Characters in the Cell from the end.
Example: If a cell contains 234 characters, Excess characters (More than 200) to be removed from the end.
Any formula or Vba program in generic form, so that i can limit the number of Characters to as required.
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"
Is there way i can have a macro attatched to a button that when clicked, will copy BX:BX on sheet1, remove all the blank cells and put it on D:D on sheet2. Next time it is clicked, it will put BX:BX on to the next available column after removing the blanks and so on, filling a column at a time on sheet2?
If the above is easy, it would be really good if it could paste into columns until it reaches column H and then go back to D again but paste below the data already there, then E, but below, etc. I think though that the last bit is asking a bit much and is just cosmetic and easier to print, so dont worry too much.
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'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?
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.
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 (_).
What I need is a macro that will remove any instance of "R", "-1", "-2", "-3", "-4" from the end of a cell. neither of the 5 values listed in the last sentence are present, the the cell will be unchange. So, after running, the above values would look like this:
Is there a function that can remove all text and other characters from cell and only keep the numbers? The numbers can be randomly in the cell so not only in the end or beginning.
I am trying to do a vlookup. I receive a report that gives the data as hyperlinks. I run a macro to remove the hyperlinks. I'm then left with a name that i want to match using a vlookup to get other data. The names are Chinese characters. I have tried using trim and clean function but its still leaving a space after the name. If i fo in manually and delete the space at the end the vlookup works fine. How to remove the spaces? i have about 5000 rows.
