1. I am trying to remove some unwanted characters in the cells. I have in column A a list of stock counters preceeded by their tickers e.g.
1D42 - CM TEL
1G47 - CSE GLOBAL
Prior to exploring VBA, i was manually doing mid(A1,8,20) to remove those characetrs before the actual name starts but as my list goes on indefinitely, the numbers grow and now i have a few thousand stocks in my portfolio. Also, for some names i cannot use mid(A1,8,20) anymore if the tickers are longer than 5 or 6 characters or are simply shorter.
So now I am trying to see what code will enable me to remove all characters before the actual name i.e one character space right after the Hypen. The hypen only appears ONCE in the cell as the names will not contain them.
2. In addition, after all is removed, what is the code that will be needed to do an automatic Sort of the data corresponding to the stocks (From column A to K) based on alphabetical order?
I am trying to remove ’ę characters from my excel sheet, as they are preventing me from using the text to columns function correctly. I have attached an example extract of data, could someone let me know how I can remove these, as the find and replace wizard in excel doesn't allow me to do this?
Currently I've been able to remove the prefixes and suffixes using the replace option as those were consistent across the data set, I'm now left with a series of numbers that look like this:
In most cases I need to remove the information after the last period. Some of the data has a space at the end, instead of a period and then some have no space or period but have the letter that needs to be dropped. I'm using xl 2011 for MAC.
See the attached file. I regularly receive input files which I have to reformat and upload to a website as a .csv. The website does not like the / between the characters on the Input Data tab. Is there a way to automatically search and remove them and leave the rest of the characters intact? The columns do not always appear in the same order and there could be more or less columns. On the Import Template tab, columns A-I will always be there, so ideally the code should make the changes from the Input Data tab and copy the present columns (J thru whatever) header and data over to the Import Template tab starting in 'J1'. I think that VBA would be the best way to go due to the variable nature of the columns.
I am trying to create a CSV file to import all of our workers into a payroll software. Unfortunately the list of workers that I have been given to work with has a first and a last name together in one cell. I need to split them into separate cells so the data ends up in the right field in our software. By importing external data and separating the names with the comma as a delimiter, I have ended up with the first name in one cell and the second in another, which is exactly what I need. However, this process also ends up putting a quotation mark in front of the first names in the cells in column A, and a quotation mark after the second name in the cells in column B.
I remember to removing trailing spaces with a macro once, and I am hoping the same theory can be applied to make two macros - one to remove preceding quotation marks, and one to remove trailing quotation marks. The only trouble is, I know no VBA whatsoever, and don't even know how to make a macro unfortunately. Would someone mind letting me know if what I need is possible, and then - literally step by basic step - explain how to make a macro and provide the code to paste into it?
I'm needing some code that will remove an open quotation mark from the numerical value found in column A. This will be for every row with a numercal value.
example problem: "360015 example answer: 360015
All numbers will be 6 digits
And I've got another issue with numerals in column C. To the left side of the Cell there maybe a Number or Letter. To The Right side of the cell there will be a single or double digit number. Both will be separted by some space (6-8 spaces). As in the examples below: (again what you see on each line is in one cell in col. C.
R 59 E 6 0 4
I need the value on the left side of the cell to be moved concenate/merge with the values in column B.
There's will be approx 2000 rows that will need to be checked for this occurrance. Since there's so many rows, I'd really would like to use VBA and not formulas to accomplish this.
Let us assume x and y are strings. Now let us make x equal to a small sentence (ie. full of words and spaces). I now need to somehow make y equal to x, but with the last word removed. I need to do this in VBA.
I need to remove the letter within the middle of a persons name.
For example, if the person is called 'Michael J Fox' then it needs to be edited to show only 'Michael Fox', if the person is called 'George W Bush' then only 'George Bush' should be displayed etc. However the persons name should remain as is if a middle character initial does not exist.
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"
I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.
My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*
(If it could delete all unwanted characters except for letters/digits) There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.
So for example; if there was a phrase in the list like ....
I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:
00 - Preconstruction 01 - General Conditions
I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.
Second thought:
If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.
I'm trying to automate a redundant task. I have several Word files in one central folder. I need to open each file, remove all paragraph marks (via find/replace all), copy the result, and paste it into an Excel sheet. This process will be repeated for each file in the folder.
The code I have is almost there, however, I can't get it to do the find/replace. Below is an extract of my code for the Word application part. The code for the copy and paste will be inserted after the replace action works:
Dim Wapp As Object Dim i As Integer
WorksheetsLocation = TextBox1.Value ' this is the folder location brought in from an input box
On Error Resume Next Set wApp = GetObject(, "Word.Application") If Err.Number 0 Then 'Word isn't already running Set wApp = CreateObject("Word.Application") End If On Error GoTo 0
I have come up with this to Trim all of the data from rows 2:30 removing any trailing spaces after the last word in each cell. The macro takes a couple of minutes to run have I got something wrong that is making it run slowly or does the Trim process just take longer?
Sub TRIM_RANGE() Dim myRange As Range Dim myRow As Range Sheets("CAMPAIGNS_2007").Select Set myRange = Range("2:30") If myRange Is Nothing Then Exit Sub Application. ScreenUpdating = False myRange.Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False For Each myRow In myRange.Columns If Application. CountA(myRow) > 0 Then myRow.TextToColumns Destination:=myRow(1), _ DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) End If Next myRow Application.ScreenUpdating = True End Sub
As you can see, in D5, I have the charactor chain SRG-DC01-RA-xxxxxxx, and in D6 I have the charactor chain SRG-DC43-RA-xxxxx.
With the formula that I have entered, I would have thought that E5 would have generated "Lawnton". I don't want the formula to pick up the SRG, the RA, or the number, just the DCXX component.
This is what I have now: =IF(OR(ISNUMBER(SEARCH({"DC43","DC01"},D5))),"Erskine Park","Lawnton")
which has not changed a thing from: =IF(OR(ISNUMBER(SEARCH({"DC43"},D5))),"Erskine Park")
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document. For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document 'or Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
I have certain cells in column ‘D’ with a ‘nine digit code’ then a ‘space’ and the word ‘sum’ e.g. ‘123456789 sum’.
What I require is the formula to enter in ‘formula is’ when you select Conditional Formatting so the cells in column ‘E’ changes to a green background if the corresponding cell in column ‘D’ has text in it and the last three characters are the word ‘sum’
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
1. I have a excel file with Japanese words in column A and their English equivalents in column B.
2. I am trying to create a Macro and assign it to a button. On pressing the button Macro should be able to:
3. open a form where I can enter location of a word file.
4. Macro should open the word file specified in (3).
5. Macro will pick up first Japanese word from excel file (Sheet 1 - A1)
6. Search for that word in Word file
7. Replace the Japanese words in Word file with their English equivalent from excel (B1)
8. Then it should search for second word (A2) and replace with its equivalent (B2) and so on till it reaches last filled cell in excel file column A.
After a lot of search I could find a code from net (Below), made a few changes, but it is not working.
======================================================== Private Sub OK_Click() ' Requires a reference to Microsoft Word xx.x Object Library Dim sFile As String
