Delete First X Characters In Cells
Oct 10, 2007
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
View 9 Replies
ADVERTISEMENT
Feb 19, 2007
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
View 9 Replies
View Related
Dec 28, 2005
"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?"
Example:
AAAASAHDASK
AAASAHDASK
AASAHDASK
ASAHDASK
SADHASAAAA
ADHASAAAA
DHASAAAA
View 14 Replies
View Related
Oct 20, 2012
From the names listed in column A, in the table listed below, I wish to delete everything
to the right of the first two characters.
So the new column A would look this.
A
1
E HAZARD
2
F LAMPARD
3
F MALOUDA
[code].....
View 9 Replies
View Related
Nov 17, 2006
I have a formula which I have been using for testing up till now. =IF(LEN(Q3)
View 4 Replies
View Related
Dec 8, 2007
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.
View 2 Replies
View Related
Jan 22, 2014
See the excel sheet [URL]
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
View 14 Replies
View Related
Dec 28, 2011
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?
View 4 Replies
View Related
Mar 13, 2013
I ve got a list multuple values. All of them contain 00 before the actual value. How can I delete those two 00.
Here's an example
Currently Desired Outcome
0030148099 30148099
0030148099 30148099
0030148100 30148100
0030148100 30148100
0030148101 30148101
View 2 Replies
View Related
Sep 5, 2013
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
View 6 Replies
View Related
May 14, 2014
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
View 6 Replies
View Related
May 4, 2006
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.
View 5 Replies
View Related
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.
View 4 Replies
View Related
Jul 31, 2014
in VBA how would I script to search down column and if the cells starts with "20" delete the first 13 characters?
also if it finds more than 10 empty rows it cuts the loop otherwise I may be waiting a while
View 7 Replies
View Related
Jun 11, 2008
I have one column with many numbers. Some have one dash and some have two.
Example:
123-123456-65
012-789546-1
98B12354-889
Is there a way that I can remove all characters after the last – (dash) in the number?
Example:
If number is 123-123456-65
Then 123-123456
If number is 98B12354-889
Then 98B12354
If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.
View 3 Replies
View Related
Feb 14, 2012
How can I delete characters unallowed in file names in cell text all in one go instead of below long macro. (i.e. can you minimize the below vba)
These characters that I wanna delete
"/", "", ":", "*", "?", "< ", ">", "|"
Code:
Range("A1").Select
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
[Code].....
View 6 Replies
View Related
May 20, 2008
I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.
With . Cells(Lrow, "B")
If Not IsError(.Value) Then
'****
If LCase(.Value) = LCase("D") Then .EntireRow.Delete
View 5 Replies
View Related
Nov 5, 2008
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
View 9 Replies
View Related
Jan 18, 2010
I have some text that I need to format, at the moment it looks like this:
[note name="Person A" label="0"[Person A's information]/note]
[note name="Person B" label="0"[Person B's information]/note]
etc
I would like to extract the Name (Person A) and the actual note itself (Person A's information) into two columns. I tried using "Text to Columns" as I have in the past, but there is a problem. Many of the information fields contain hundreds of characters, with the largest being ~2500 characters. The text to columns works does its job but cuts out a lot of data (anything over 255 characters).
I'm using MS Excel 2000
The text contained HTML codes, so they have not appeared correctly I have replaced with []
View 9 Replies
View Related
Apr 18, 2008
I have a spreadsheet with approx 1000 rows of data. One column contains ledger codes that can be up to 9 characters. I need the column to only show the first 5 characters.
View 2 Replies
View Related
Dec 10, 2007
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
View 5 Replies
View Related
Sep 8, 2009
I have a column of about 3000 numbers that need to be converted to bar codes. In order for the codes to work, I need to place a set of parentheses "()" around each number in the column. Do I need to do this by hand, or is there a function/script for this? Using Excel 2003
View 2 Replies
View Related
Jan 24, 2014
There are three questions in Comment boxes in the file. The bottom line is that the final item name in column F can only be 50 characters long including spaces. As I am entering the information in columns B,C & D it would be great to be able to see the numbers of spaces I have left to enter characters in column H without having to hit "Return" and leave the cell.
The first question may need a Macro so excuse me that it is included in the Formula section.
View 2 Replies
View Related
Aug 4, 2009
I have recently purchased a new GPS for my fishing boat. I am trying to transfer my old coordinates from one unit to the other. I am using a .csv file to achieve this. Can some kind please let me know (and possibly tell me) if there is way to insert characters and spaces in to multiple cells. I have over 800 individual coordinates to modify to the new gps and this would take a long time to achieve individually.
I need to replace this: 12.34.567 to this: 12 34.567 N
and this: -1.23456 to this: 001 23.456 W
View 9 Replies
View Related
Jul 3, 2012
Cells in the range of BG8:BP8 either have a "W" or an "L" in them. I want to have cell BO9 display the total number of Ws and cell BP9 to display the total number of Ls.
View 3 Replies
View Related
Mar 28, 2007
I've looked through some of the archieves and couldn't find anything like my request.
I need help filtering through 3+ columns (it varies) of data and look for strings of data that have 3 or more repetative characters or sets of repetative characters.
eee
boook
bbbbbbb
Mi...ke
111222
123###789
jpppp@myemail.com
Excelll
If this is possible, can it go one step further and do the same for:
ababab
ckckck
F&F&F&
121212
Jkjkjk
View 9 Replies
View Related
Feb 10, 2009
I need to remove the last 2 digits from a cell that maybe different lengths. For example:
PL26 7QS
ST20 0AW
LE4 8LF
PE30 3WH
CF31 3AY
View 3 Replies
View Related
Sep 28, 2006
Column A in Excel has loads of numbers all in this format 971-417. I need to have each of those two numbers in a separate cell from each other(and without the hyphen of course).
Column A Needs to be: Column A Column B
971-417 971 417
In short, I'm looking for a quick way to put each number in its own cell for hundreds of rows. Are there formulas I can use to do this or does it have to be done slowly, one at a time, stripping the information from one cell to another?
View 6 Replies
View Related
Feb 22, 2008
I need a formula that will turn a cell green if either A*, A, B or C is in it.
View 5 Replies
View Related
Jun 17, 2008
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.
View 3 Replies
View Related
