Truncate Left Characters in Cell Value
 

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.

Truncate Left Characters in Cell Value
 

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.

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

Truncate Left Characters in Cell Value
 

"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

Truncate Left Characters in Cell Value
 

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