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

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

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

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

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

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

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

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)

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)