Removing text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a variable and I need to remove everything after the last "_" in a
string including the "_".

Example
abc_def_ghi_jklmnop

Result: abc_def_ghi
 
=MID(B1,1,FIND("#",SUBSTITUTE(B1,"_","#",LEN(B1)-LEN(SUBSTITUTE(B1,"_",""))))-1)

data in B1

HTH
 
One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)
 
I put this into the cell formula and it worked fine.

What I need it for is a macro in VBA. I tried it in there and it gave the
error on Substitue and I replaced that with Replace but now Its giving me an
error on the Find .

Do you have any suggestions?
 
Range("c1") = Left(Range("a1"), Application.Find("^",
Application.Substitute(Range("A1"), "_", "^", _
Len(Range("a1")) - Len(Application.Substitute(Range("a1"), "_", "")))) - 1)
 
Great thanks

Toppers said:
Range("c1") = Left(Range("a1"), Application.Find("^",
Application.Substitute(Range("A1"), "_", "^", _
Len(Range("a1")) - Len(Application.Substitute(Range("a1"), "_", "")))) - 1)
Click to expand...
 
I put this into the cell formula and it worked fine.

What I need it for is a macro in VBA. I tried it in there and it gave the
error on Substitue and I replaced that with Replace but now Its giving me an
error on the Find .

Do you have any suggestions?
Click to expand...

Here's a different approach:

=================================
Option Explicit
Sub foo()
Dim c As Range
Dim s() As String

For Each c In Selection
s = Split(c.Text, "_")
ReDim Preserve s(UBound(s) - 1)
c.Offset(0, 1).Value = Join(s, "_")
Next c

End Sub
=================================
--ron
 
Here's a different approach:

=================================
Option Explicit
Sub foo()
Dim c As Range
Dim s() As String

For Each c In Selection
s = Split(c.Text, "_")
ReDim Preserve s(UBound(s) - 1)
c.Offset(0, 1).Value = Join(s, "_")
Next c

End Sub
=================================
--ron
Click to expand...

Might want to do an error handler:

=========================
Option Explicit
Sub foo()
Dim c As Range
Dim s() As String

On Error Resume Next
For Each c In Selection
s = Split(c.Text, "_")
ReDim Preserve s(UBound(s) - 1)
c.Offset(0, 1).Value = Join(s, "_")
Next c

End Sub
==========================
--ron
 
Back
Top