String Slicing (How to)

[DaveUK]

I have a range of undefined length (rows) in Column A of worksheet "Data".

What i want to do is use the left$ type function so all text in each row in the range (Column A) is restricted to the first 24 characters only.

Please could someone advise how to do this.

Many thanks in advance.

 

[Norie]

Have you looked at Data>Text to Columns... using Fixed Width.

 

[Cbrine]

=left(A1,24)

Just copy that formula down the entire column of data.

HTH
Cal

 

[SteveD]

In VBA:
sub myLen24()
'Define the length on the rows assume data is in column a cell2 and down
Set myRange = Range(Range("A2"), Range("A2").End(xlDown))
'Set the length as desired in column b
'you may want to insert a column here if there is already data in column b
myRange.Offset(0, 1).FormulaR1C1 = "=(LEFT(RC[-1],24))"
end sub

Then if desired, copy column b and paste sepcial values into column a and delete column b

 

[Norie]

If you want VBA then something like this might work:

Sub Make24
Dim rng As Range
Dim c As Range
Dim LastRow

     LastRow = Range("A65536").End(xlUp).Row

     Set rng = Range("A1:A" & LastRow)

     For Each c in rng.Cells
          c.Value = Left(c.Value, 24)
     Next c

End Sub

If there are lots of entries in column A this could be quite slow.

 

[Cbrine]

Oh Yeah,

Sub TrimCell()
Dim cell as range
For each cell in range("A1",range("A65535").end(xlup))
cell.value = left(Cell.value,24)
Next
End sub

This will trim the entire range of cell to the 24 characters, no cutting or pasting.

Still like Norie's idea though.

HTH
Cal

One upsmenship at it's best Of course Norie beat me to it.