Delete 1st character if string is longer than 6 characters
 

Hello,

I have a list of values in column C (275,372 cells) and would
like to know if there is a way to look at each cell and delete the 1st
character if the string is longer than 6 characters. Below is what
the before sample would look like.

009075
009130
0009767
010471
010489
011640
013373
013824
017424
019038
020634
020642
020663
0020711
020723

So the above list will look like this after:

009075
009130
009767
010471
010489
011640
013373
013824
017424
019038
020634
020642
020663
020711
020723



Thanks you for any help you can provide,

DIDS

Delete 1st character if string is longer than 6 characters
 

Hello DIDS,

Am Wed, 11 Apr 2012 10:33:23 -0700 (PDT) schrieb DIDS:

I have a list of values in column C (275,372 cells) and would
like to know if there is a way to look at each cell and delete the 1st
character if the string is longer than 6 characters.

try:
Sub myString()
Dim LRow As Long
Dim c As Range

LRow = Cells(Rows.Count, 3).End(xlUp).Row
For Each c In Range("C1:C" & LRow)
If Len(c) 6 Then
c = Right(c, Len(c) - 1)
End If
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Delete 1st character if string is longer than 6 characters
 

On Wed, 11 Apr 2012 10:33:23 -0700 (PDT), DIDS wrote:

Hello,

I have a list of values in column C (275,372 cells) and would
like to know if there is a way to look at each cell and delete the 1st
character if the string is longer than 6 characters. Below is what
the before sample would look like.

009075
009130
0009767
010471
010489
011640
013373
013824
017424
019038
020634
020642
020663
0020711
020723

So the above list will look like this after:

009075
009130
009767
010471
010489
011640
013373
013824
017424
019038
020634
020642
020663
020711
020723



Thanks you for any help you can provide,

DIDS

Since your example only shows six or seven character values, I wonder if you really want exactly what you write.

If you want to reduce all of your entries to six characters (or less), you could use:
========================
Option Explicit
Sub SixMax()
Dim rSrc As Range, c As Range
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each c In rSrc
c.Value = Right(c.Value, 6)
Next c
End Sub
========================

If some entries are less than six, and some more, but you want all entries to be six characters, then you could use:

==========================
Sub SixExact()
Dim rSrc As Range, c As Range
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each c In rSrc
c.Value = Right("000000" & c.Value, 6)
Next c
End Sub
===========================

And if you really want to remove just one from the right if the length is longer than six (so an eight character string becomes 7; and a 7 character string becomes 6; then)

============================
Sub RemoveOne()
Dim rSrc As Range, c As Range
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each c In rSrc
c.Value = Mid(c.Value, IIf(Len(c.Text) 6, 2, 1))
Next c
End Sub
===========================

Delete 1st character if string is longer than 6 characters
 

Hi Claus Busch,


Your code worked perfectly. Thank you very much for your
assistance.


Thanks,

DIDS

Delete 1st character if string is longer than 6 characters
 

Hi Ron Rosenfeld,

Thank you very much for your assistance.


Thanks,

DIDS