Truncate text

[Guest]

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

 

[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)

 

[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

 

[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

 

[Guest]

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

 

[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)

 

[Frank Kabel]

Hi
you have to loop through the range