Truncate Left Characters in Cell Value

[Ray Clark]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Ray Clark]

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

 

[sebastienm]

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

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