| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing text | Excel Discussion (Misc queries) | |||
| removing blanks at the end of text | Excel Worksheet Functions | |||
| Removing Text | Excel Worksheet Functions | |||
| Please help with removing text string | Excel Discussion (Misc queries) | |||
| Removing text characters | Excel Worksheet Functions | |||
Linear Mode
