Removing text
 

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

Removing text
 

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

data in B1

HTH

"pokdbz" wrote:

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

Removing text
 

One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)

In article ,
pokdbz wrote:

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

Removing text
 

On the last substitue its giving an error: Function or Sub not defined.

"JE McGimpsey" wrote:

One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)

In article ,
pokdbz wrote:

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

Removing text
 

=left(a1,11)

Vaya con Dios,
Chuck, CABGx3



"pokdbz" wrote:

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

Removing text
 

Check for typing errors: it worked fine for me.

"pokdbz" wrote:

On the last substitue its giving an error: Function or Sub not defined.

"JE McGimpsey" wrote:

One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)

In article ,
pokdbz wrote:

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

Removing text
 

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?

"Toppers" wrote:

Check for typing errors: it worked fine for me.

"pokdbz" wrote:

On the last substitue its giving an error: Function or Sub not defined.

"JE McGimpsey" wrote:

One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)

In article ,
pokdbz wrote:

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

Removing text
 

Range("c1") = Left(Range("a1"), Application.Find("^",
Application.Substitute(Range("A1"), "_", "^", _
Len(Range("a1")) - Len(Application.Substitute(Range("a1"), "_", "")))) - 1)

"pokdbz" wrote:

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?

"Toppers" wrote:

Check for typing errors: it worked fine for me.

"pokdbz" wrote:

On the last substitue its giving an error: Function or Sub not defined.

"JE McGimpsey" wrote:

One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)

In article ,
pokdbz wrote:

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

Removing text
 

Great thanks

"Toppers" wrote:

Range("c1") = Left(Range("a1"), Application.Find("^",
Application.Substitute(Range("A1"), "_", "^", _
Len(Range("a1")) - Len(Application.Substitute(Range("a1"), "_", "")))) - 1)

"pokdbz" wrote:

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?

"Toppers" wrote:

Check for typing errors: it worked fine for me.

"pokdbz" wrote:

On the last substitue its giving an error: Function or Sub not defined.

"JE McGimpsey" wrote:

One way:

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "_", "^", LEN(A1) -
LEN(SUBSTITUTE(A1, "_", "")))) - 1)

In article ,
pokdbz wrote:

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

Removing text
 

On Mon, 6 Aug 2007 10:32:17 -0700, pokdbz
wrote:

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?

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

Removing text
 

On Mon, 06 Aug 2007 20:00:30 -0400, Ron Rosenfeld
wrote:

On Mon, 6 Aug 2007 10:32:17 -0700, pokdbz
wrote:

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?

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

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