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

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" <(E-Mail Removed)> skrev i en meddelelse
news:j71fo5$ohf$(E-Mail Removed)...
> 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
>>
>>

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

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

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" <(E-Mail Removed)> skrev i en meddelelse
news:j71k2s$1md$(E-Mail Removed)...
> 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

Hi Isabelle,

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

Kaj Pedersen


"isabelle" <(E-Mail Removed)> skrev i en meddelelse
news:j71k4v$ohf$(E-Mail Removed)...
> 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
>

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)

Hi Rick,

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

Kaj Pedersen



"Rick Rothstein" <(E-Mail Removed)> skrev i en meddelelse
news:j71sbd$9lf$(E-Mail Removed)...
> 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)