Truncate Left Characters in Cell Value
On Wed, 26 Dec 2007 13:38:00 -0800, Ray Clark
wrote:
I have a number of text cells and I need to be able to truncate a number of
characters from the left hand side of the value. eg. MB99_Prod_PGP_Trans
needs to be truncated to "_Prod_PGP_Trans"
I am having trouble trying to find the VBA code to do this.
Can anyone help as I need to get this done in a hurry.
It would have been helpful if you had clarified the rules for truncation. From
what you've written, I will assume that you want everything starting with the
first underscore.
That being the case,
Trunc = Mid(str, InStr(1, str, "_"), 255)
will do this.
=====================
Function Trunc(str As String)
Trunc = Mid(str, InStr(1, str, "_"), 255)
End Function
Sub foo()
Debug.Print Trunc("MB99_Prod_PGP_Trans")
End Sub
=======================
If you are looping through a bunch of cells, then:
=========================
Sub foo()
Dim c As Range
For Each c In Range("A1:A100")
If InStr(1, c.Text, "_") 0 Then
c.Offset(0, 1).Value = Mid(c.Text, InStr(1, c.Text, "_"), 255)
End If
Next c
End Sub
============================
--ron
|