Limit a cell

  • Thread starter Thread starter bt707
  • Start date Start date
B

bt707

I have a long column of names that are 8 to 25 Characters long and i
need to find a way to take the first 15 Characters and cut off the
remaining so that all the names are 15 characters or less.

Thanks
 
One way:

Choose Data/Text to Columns... Select the Fixed radio button and
click Next. Set a break after the 15th character. Click Next. Click
any but the first column and select the Do not import radio button.
Click Finish.
 
Don,

This is something I've not taken the time to look into, but it's interesting
that you have to use c.value for this to work, not just c. Since c is a
range object, and the default property of a range object is .value, you
should be able to write c = left(c,15). I wonder if this is a failure of
VBE in a for/next loop to use the .value default for an unspecified
property/method.

Thhe following works. Not necessary to use c.value. It's not in a for/next
loop:

Dim c As Range
Set c = Range("A1")
c = Left(c, 5)

Specifying the property/method of all objects is the best programming
practice, but our fingers might not last our lifetimes typing all the extra
stuff. :)

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Don Guillett said:
for each c in selection
c.value=left(c,15)
next
Click to expand...
 
As I wrote it, the .value is needed. A test of yours for more than one cell
failed.
If I dim c as range then the .value is not needed

Sub limitem2()
Dim c As Range
For Each c In Selection
c = Left(c, 5)
Next
End Sub

Earl Kiosterud said:
Don,

This is something I've not taken the time to look into, but it's interesting
that you have to use c.value for this to work, not just c. Since c is a
range object, and the default property of a range object is .value, you
should be able to write c = left(c,15). I wonder if this is a failure of
VBE in a for/next loop to use the .value default for an unspecified
property/method.

Thhe following works. Not necessary to use c.value. It's not in a for/next
loop:

Dim c As Range
Set c = Range("A1")
c = Left(c, 5)

Specifying the property/method of all objects is the best programming
practice, but our fingers might not last our lifetimes typing all the extra
stuff. :)
Click to expand...
 
http://support.microsoft.com/default.aspx?scid=kb;en-us;107903&Product=xlw
Can't Set Value Property in For Each with Variant Type

--
Regards,
Tom Ogilvy


Earl Kiosterud said:
Don,

This is something I've not taken the time to look into, but it's interesting
that you have to use c.value for this to work, not just c. Since c is a
range object, and the default property of a range object is .value, you
should be able to write c = left(c,15). I wonder if this is a failure of
VBE in a for/next loop to use the .value default for an unspecified
property/method.

Thhe following works. Not necessary to use c.value. It's not in a for/next
loop:

Dim c As Range
Set c = Range("A1")
c = Left(c, 5)

Specifying the property/method of all objects is the best programming
practice, but our fingers might not last our lifetimes typing all the extra
stuff. :)
Click to expand...
 
Back
Top