unwanted concatenation

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Need to delete appended character string from valid data
in cells in one column (B), so B2:B500. Data in the cells
is of varying length, but the unwanted appended char
string is always 14 characters (including the preceding
<space>). In the following example, I want to delete
everything following "Smith John". Characters vary within
the parenthesis, but always this format.

Example:
Smith John (x_cc24_g011)

Is there a way to delete the unwanted string by counting
from the right? Or, by copying the data to the left of
this string to another column?

Thanks for your help,
Randy
 
Try this Randy

Sub test()
Dim cell As Range
For Each cell In Range("B2:b500").SpecialCells(xlCellTypeConstants)
On Error Resume Next
cell.Value = Left(cell.Value, Len(cell.Value) - 14)
On Error GoTo 0
Next
End Sub
 
Much thanks Ron! Works beautifully.

Randy
-----Original Message-----
Try this Randy

Sub test()
Dim cell As Range
For Each cell In Range("B2:b500").SpecialCells (xlCellTypeConstants)
On Error Resume Next
cell.Value = Left(cell.Value, Len(cell.Value) - 14)
On Error GoTo 0
Next
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Randy" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top