Delete Unwanted Characters Within Certain Criterias
Jun 20, 2007
I have a spreadsheet that i manually edit each and everyday e.g.
A B C
EABGL/UD NDT254892
MRMR/RUS/ELQNS259762
LSL/UW/B LQNS267259
WWEX/UQ bbr263666
LWL/KL/B 270407
MYTCJ/UB NDT271774
LNL/SB/UB HLC - 271955
SMMQD/WT HLC - 269516
EACO/TN/UGBBR257827
NILVA/UC EUi273645
For everything that doesnt equal EM, LN, LW and TH in column A, everything should be deleted in column B.
For the remaining EM, LN, LW and TH, i would then like it to delete / (forward slash and all characters after this) so that this would make my life easier.
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'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.
I'm having the following little problem, and i'm hoping that someone here can help me solve it.
I have a number of rows containing data starting at row 1. I already made VBA code which finds the last row containing data. That row number is stored in the variable "LastRowWithData".
What i want to do now is the following.
In column B (in row 1 to the row number stored in "LastRowWithData"), there are long code's which all have to be checked for unwanted characters.
Some of these unwanted characters are the "I" and "O" characters both in upper case and lower case.
If these characters are found a messagebox has to appear, saying something like "Illegal character found in cell ____".
If more than one cell containing unwanted characters are found, either more messageboxes with the cell coordinates have to be displayed or one messagebox displaying all the cell coordinates in which the unwanted characters are found.
Im planning to use a while - wend statement to check all cells for unwanted characters. (While ActiveCell.Row LastRowWithData)
Is there a simple way to do this in (Excel 2003) VBA ?
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 ....
Is there an Excel function that can strip a specificed unwanted character from a text string to leave all the other characters excluding the unwanted one?
e.g. if the original text strings were (in separate cells within a workbook):
I am using the following code to filter for data I do not want and then to delete those rows and show remaining data. It works fine except when the filter comes up empty and there is no unwanted data to delete.how to improve this code to accomodate this situation?
Selection.AutoFilter Field:=4, Criteria1:=">" & dweekend, Operator:=xlAnd Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete ActiveSheet.ShowAllData Thank you for reading
I am using an autofilter to delete chunks on unwanted data, by using Range(Selection, Selection.End(xlUp)).Select however it picks up the header row. Is there anyway I can either get the selection of data to select one less line, or a way to get it to leave the header line?
I am aware this is probably very simple, but I haven't been able to find a usable macro yet. I would like the data in the workbook that is not "Tuesday" (column L) and is not "3" or "4" (column I) to be deleted. All rows that have "Tuesday" and either "3" or "4" should not be deleted. Each row that is not deleted must have Tuesday in column L. All other cells are blank intentionally. The workbook will remain the same titled WKBK1 (no need to put the output data in a separate workbook nor separate spreadsheet).
Do you know what the macro would be so I don't have to copy and paste a formula over a large number of cells?
I'm using excel 2010. I'm working with columns of values where most of the values are numbers - which is fine, and there are some numbers that have a "p" at the start of them.
e.g. Column has 49, 52.2, p56.7, 34
OK, I want to preserve the 56.7 but I want to delete the "p". I'm thinking I've got a mental block as to how to delete the unwanted "p"s but I can't think of how to do it at the moment! My code goes as follows:
If Left(Cells(1,1),1) = "p" then 'delete the "p" and leave the remaining number in tact End if
I want to delete some unwanted rows on closing my excel file. the blank rows are between set of datas. so it has to check all the blank rows, delete it until the last one. example
data blank rows data blank rows data blank rows data
My spreadsheet has 600rows and the vertical scroll bar ends at row 459424. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete.
But the unwanted rows are still there and my vertical scroll bar is not correlated to the 600 rows.
I have a keyword list in ColA. about 25,000 phrases. What I want to do is create a macro button. when i click on it a pop up box would appear called "Deleting Characters" It will have to 2 rows, 1 saying "More Than",, the other saying "Less Than"
So, beside these words are input boxes. So I can type the numbers "2" and say "50" So it will look at all the rows of data in my ColA (Always from A3 downwards) and deletes all rows with less than 2 characters and more than 50 characters. When it is finished a floating box appears saying "123 Characters Less Than 2 Deleted" "101 Characters More Than 50 Deleted"
Sub NoOfCharacters() Dim lastrow As Long, TotP, TotC, AvC lastrow = Cells(Rows.Count, 1).End(xlUp).Row If lastrow < 4 Then MsgBox "Nothing to sort!" Exit Sub End If Range("B1").EntireColumn.ClearContents Range("B3").Formula = "=LEN(A3)" Range("B3").AutoFill Destination:=Range("B3:B" & lastrow) Range("A3:B" & lastrow).Sort key1:=Range("B3"), order1:=xlAscending, Header:=xlGuess TotP = lastrow - 2 TotC = Application.Sum(Range("B3:B" & lastrow)) AvC = TotC / TotP MsgBox "Total No Phrases: " & Chr(9) & Chr(9) & Chr(9) & TotP & Chr(13) & "Total No Characters: " & Chr(9) & Chr(9) & TotC & Chr(13) & "Avg No Characters Per Phrase: " & Chr(9) & AvC End Sub
"Is there some VBA code which could delete all first, second or third characters of a text? Could it be done to the three last characters from this same text and these be displayed on reverse order?"
I am trying to write a macro that will go through each cell in a column with the following format "| 12- 4" or "| 60-11" and will remove the first to character "| ".
There numbers are lengths, the first being feet the second inches. I would like to achieve in a separate column the numerical length (with decimals) multiplied by 1%. My code is as follows I just need help on the conversion.
Sub newLength() Dim LR, lrow As Integer Dim feet, inches, Line As Double
LR = Range("B65536").End(xlUp).Offset(1, 0).Row For lrow = LR To 2 Step -1 If Cells(lrow, "D") <> Cells(lrow - 1, "D") Then Rows(lrow).Insert Shift:=xlDown End If feet = Left(Cells(lrow, "E").Value, 4) inches = Right(Cells(lrow, "E").Value, 2) / 12 'I need to debug this line, but I suspect there is more I need to do. Cells(lrow, "F") = feet + inches Next lrow End Sub
Sub rightval() Dim myrange, mycell As Range Set myrange = Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp)) For Each mycell In myrange If Right(mycell.Value, 1) = "." Then mycell.Value = Left(mycell.Value, Len(mycell.Value) - 3) End If Next mycell End Sub
I'd like to delete the last three values of one cell only if it contains a period.
I have set of data Pasted in 4th row, in the top row 44 columns values assigned i want move data from set of data to different column among these 44 columns
Like "Service Order ID" is 1 column in set of data ,it move to second column of top row
Some of column need to delete. (Service Order Type,Service Order Description,Created By,Status,Contact,Expected Delivery Date,
I need to delete all the digits before / and also the / - in the valuta columns. But this =RIGHT(B2,LEN(B2)-6) doesnt work It says > the formula you typed contains an error
I have about 2000 rows, all with an amount of words between 2 and 6. I want to limit each row to keep only the first TWO words. Is there a way to do this?
If not, is there a way to limit each row to only keep the first 11 characters?
I can't get this code to work which I want to delete the entire row if the cell in the specified range contains only 3 characters;
Code:
Sub CharCount() Dim cell As Range Dim bottomK As Integer bottomK = Range("D" & Rows.Count).End(xlUp).Row Dim rng As Range Set rng = Range("D2:D" & bottomK) For Each cell In rng If Len(cell) = 3 Then EntireRow(bottomK).Delete End If Next cell End Sub
I need a formula which can clean up a huge data set. Essentially i need to delete the entire word which contains the characters "aceae". note that "aceae" is a suffix, but i need to delete the entire word not just the suffix, plus keep the rest of the string. i have tried the "find and replace" function of excel with wildcard, but that deletes everything before/after without deleting the entire word. i have tried a combination of formulas to isolate the unwanted words, but that method is inefficient and inaccurate. below is a schematic of what im looking to do:
Column A ------------------------------ Column B l. planeri asteraceae africa laselva-----> l. planeri africa laselva l. planeri moraceae europe singer------> l. planeri europe singer origin l. fluviatilis bignoniaceae asia----> origin l. fluviatilis asia alternate l. fluviatilis piperaceae asia---> alternate l. fluviatilis asia
I need a way to check to see if the first four characters of cell A1 is = 2006. If it is, do not delete the row, else, delete the row. Have tried everything I can think of.
