PDA

View Full Version : Deleting words in a string



JZB
09-25-2009, 05:18 AM
Hi Guys

I have 2 sets of security IDs. One includes a Bloomberg 'yellow key' and one does not.

so for example

ERZ9 vs ERZ9 Comdty
or Z Z9 Index

I am trying to think of a way of taking the yellow key (Comdty, Index, Crncy) out of the data.

essentially i want to locate the last space in the string and delete right of that space.

Any ideas?

sorry guys not sure on this one :dunno

thanks in advance for the help

JZB

mdmackillop
09-25-2009, 05:27 AM
Sub TrimLast()
Dim Cel
For Each Cel In Selection
Cel.Value = Left(Cel, InStrRev(Cel, " ") - 1)
Next
End Sub

JZB
09-25-2009, 06:01 AM
that works a treat. thanks