Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Hi,
I want to search a range for words and numbers with more than e.g. 10 characters/digits and delete from behind until 10 characters is reached. Is this possible? Best regards, Kaj Pedersen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
hi,
For Each c In Range("A1:A10") If Len(c) 10 Then Range(c.Address) = Right(c, Len(c) - 10) Next -- isabelle Le 2011-10-11 08:36, KP a écrit : Hi, I want to search a range for words and numbers with more than e.g. 10 characters/digits and delete from behind until 10 characters is reached. Is this possible? Best regards, Kaj Pedersen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Hi,
Thank you for your suggestion. It almost does the job but not quite. In order to start the deletion of characters from the end of the word I changed "Right" to "Left", but the code is not able to deal with words containing e.g. 12 characters in which case I want only the last two characters to be deleted. I hope a minor change can solve this little problem. Regards, Kaj Pedersen "isabelle" skrev i en meddelelse ... hi, For Each c In Range("A1:A10") If Len(c) 10 Then Range(c.Address) = Right(c, Len(c) - 10) Next -- isabelle Le 2011-10-11 08:36, KP a écrit : Hi, I want to search a range for words and numbers with more than e.g. 10 characters/digits and delete from behind until 10 characters is reached. Is this possible? Best regards, Kaj Pedersen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Hi Kaj,
Am Tue, 11 Oct 2011 16:05:05 +0200 schrieb KP: In order to start the deletion of characters from the end of the word I changed "Right" to "Left", but the code is not able to deal with words containing e.g. 12 characters in which case I want only the last two characters to be deleted. try: Set myRange = Range("A1:B20") For Each rngC In myRange If Len(rngC) 10 Then If IsNumeric(rngC) Then rngC = Left(rngC, 10) * 1 Else rngC = Left(rngC, 10) End If End If Next Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
hi,
For Each c In Range("A1:A10") Select Case Len(c) Case 1 To 10: GoTo Nxt Case Is <= 20: Range(c.Address) = Left(c, 10) Case Is = 20: Range(c.Address) = Left(c, Len(c) - 10) End Select Nxt: Next -- isabelle |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Hi Claus,
Nothing is carried out when running your code. I will try to explain in example what I want the macro to do. If a word in a cell has 10 characters no action should be taken If a word in a cell has less than 10 characters no action should be taken If a word in a cell has e.g.12 characters - two characters from the end shall be removed If a word in a cell has e.g. 20 characters - 10 characters from the end shall be removed In other words - no matter how many characters is in the word I always want to end up with a word consisting of only ten characters. Hope this information can help to find a solution. Best regards, Kaj Pedersen "Claus Busch" skrev i en meddelelse ... Hi Kaj, Am Tue, 11 Oct 2011 16:05:05 +0200 schrieb KP: In order to start the deletion of characters from the end of the word I changed "Right" to "Left", but the code is not able to deal with words containing e.g. 12 characters in which case I want only the last two characters to be deleted. try: Set myRange = Range("A1:B20") For Each rngC In myRange If Len(rngC) 10 Then If IsNumeric(rngC) Then rngC = Left(rngC, 10) * 1 Else rngC = Left(rngC, 10) End If End If Next Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Hi Isabelle,
Unfortunately your new suggestion does not work as I want. Take a look of my desription to Claus Busch. Kaj Pedersen "isabelle" skrev i en meddelelse ... hi, For Each c In Range("A1:A10") Select Case Len(c) Case 1 To 10: GoTo Nxt Case Is <= 20: Range(c.Address) = Left(c, 10) Case Is = 20: Range(c.Address) = Left(c, Len(c) - 10) End Select Nxt: Next -- isabelle |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Give this macro a try....
Sub LeaveOnly10CharactersOrDigits() Dim Cols As Range Const RangeToProcess As String = "C3:F9" For Each Cols In Range(RangeToProcess).Columns Cols.TextToColumns Intersect(Cols, Range(RangeToProcess)), _ xlFixedWidth, FieldInfo:=Array(Array(0, xlGeneralFormat), _ Array(10, xlSkipColumn)) Next End Sub Rick Rothstein (MVP - Excel) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete characters from behind if too many
Hi Rick,
Exactly. This macro does what I Want. Thank you. Kaj Pedersen "Rick Rothstein" skrev i en meddelelse ... Give this macro a try.... Sub LeaveOnly10CharactersOrDigits() Dim Cols As Range Const RangeToProcess As String = "C3:F9" For Each Cols In Range(RangeToProcess).Columns Cols.TextToColumns Intersect(Cols, Range(RangeToProcess)), _ xlFixedWidth, FieldInfo:=Array(Array(0, xlGeneralFormat), _ Array(10, xlSkipColumn)) Next End Sub Rick Rothstein (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete characters at the end | Excel Discussion (Misc queries) | |||
delete non numberic characters | Excel Discussion (Misc queries) | |||
Delete first few cell characters | Excel Discussion (Misc queries) | |||
delete all the cell that contains more than 25 characters | Excel Discussion (Misc queries) | |||
Delete characters | Excel Programming |