Truncate text

J

JAmes

Hi Gan
How can I truncate text to the right of and including / in a cell. Woul dlike to do it through cod
Thanks!
 
B

Bob Phillips

aCtivecell.value = left(activecell.value,instr(1,activecell.value,"/")-1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Steve Garman

If you mean you want to retain everything to the left of the "/" try:

Sub test()
Dim pos&, rng As Range
Set rng = Range("A1")
pos& = InStr(1, rng.Value, "/")
If pos& Then rng.Value = Left(rng.Value, pos& - 1)
End Sub
 
R

Rob van Gelder

I think InStrRev is XL2000

Sub test()
Dim str As String

str = "abcde/fghij/klmno/pqrst"
str = Mid(str, InStrRev(str, "/"))
End Sub
 
J

James

Can this be applied to a range or do I have to loop through the range
Thanks for the response!
 
B

Bob Phillips

No, you can set a value to a range, but for this you will need to loop
through each cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top