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.

You would use the Mid (or Mid$ to return pure string values) and InStr
functions...

Answer = Mid$(CellText, InStr(CellText, "_"))

Note that, unlike the spreadsheet's MID function, you can omit the 3rd
argument... doing so returns the remainder of the string.

Rick


"Ray Clark" wrote in message
...
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.

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

"Ron Rosenfeld" wrote:

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

Rick & Ron,

Thank you both for your help this is great and will be very usefull.

Ray

Hi,
Another quick way (no looping), but more restricted though:
It assumes that the string has the shape
<characters_without_underscore_Prod<any_character s_without__Prod
i.e. the breakpooint is _Prod

then
Range("A1:A100") .replace "*_Prod","_Prod",xlpart

--
Regards,
Sébastien
<http://www.ondemandanalysis.com
<http://www.ready-reports.com


"Ray Clark" wrote:



"Ron Rosenfeld" wrote:

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

Rick & Ron,

Thank you both for your help this is great and will be very usefull.

Ray