Delete characters from behind if too many

[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

 

[isabelle]

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 :

 

[KP]

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

 

[Claus Busch]

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

 

[isabelle]

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

 

[KP]

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

 

[KP]

Hi Isabelle,

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

Kaj Pedersen

 

[Rick Rothstein]

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)

 

[KP]

Hi Rick,

Exactly. This macro does what I Want.
Thank you.

Kaj Pedersen