Delete characters from behind if too many

  • Thread starter Thread starter KP
  • Start date Start date
K

KP

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
 
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,

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
 
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.
Click to expand...

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
 
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
 
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
 
Hi Isabelle,

Unfortunately your new suggestion does not work as I want.
Take a look of my desription to Claus Busch.

Kaj Pedersen
 
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)
 
Back
Top