Deleting Portions of Data within a Cell

[Guzzlr]

Hello All...

I have an example, which I can't find any example of VBA to get a solution.
If I have a cell contents of: ACE-1D / F-R3. I'm trying to find code that will take away the "/F-R3" so just the "ACE-1D" remains.
This is one example, the next cell may have ACI-5 / 19...and so on.
So basically, I wish to keep everything to the left of the forward slash, and do away with everything else.
Thanks for the help

 

[Yongle]

with the value in A1 put formula in B1 and copy down

=TRIM(LEFT(A1,FIND("/",A1)-1))

 

[Guzzlr]

with the value in A1 put formula in B1 and copy down

=TRIM(LEFT(A1,FIND("/",A1)-1))

This is working, thank you.
However, the problem is How to put it in a VBA code format, so I can insert it into my program I currently have..
thanks for the help

 

[ldonkers]

I have created a user defined function you can try in your code. It builds on the previous reply:

Public Function TrimText(s As String)


TrimText = Trim(Left(s, WorksheetFunction.Find("/", s) - 1))

End Function

 

[Fluff]

If there is always a space before the / try

Sub GetLeft()
Dim cl As Range
For Each cl In Range("A2:A500")
   cl.Value = Split(cl.Value, " /")(0)
Next cl
End Sub

 

[Yongle]

call function provided by @ldonkers like this

to put the value in next column

Sub CallFunc()
    Dim cel As Range, rng As Range
    Set rng = Range("A1:A10")
    For Each cel In rng
        If cel.Value <> "" Then cel.Offset(, 1).Value = TrimText(cel.Value)
    Next cel
End Sub

OR
use the line of code like this

Sub Test()
    Dim cel As Range, rng As Range
    Set rng = Range("A1:A10")
    For Each cel In rng
        If cel.Value <> "" Then cel.Offset(, 1).Value = WorksheetFunction.Trim(Left(cel.Value, WorksheetFunction.Find("/", cel.Value) - 1))
    Next cel
End Sub

To overwrite the value in the original cell, replace cel.Offset(, 1).Value with cel.Value

 

[Guzzlr]

I have created a user defined function you can try in your code. It builds on the previous reply:

Public Function TrimText(s As String)


TrimText = Trim(Left(s, WorksheetFunction.Find("/", s) - 1))

End Function

Question: Why is there an "s" inside the quotes for the public function?
thanks

 

[Guzzlr]

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And sht.Name = "Template" Then
    sht.Activate
    End If
Next sht

LastRow = Range("C:E").Cells.Find("*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox " The Last Non-Blank Row Is " & LastRow

'***************** Find ACI Group ******************

With Sheets("Template")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
For cl = 7 To LastRow
   cl.Value = Split(cl.Value, " /")(0)
Next cl
End With

So I'm using this derivation which Fluff sent, but I'm getting a type mismatch. is it because Im defining two different last row's?

 

[Fluff]

I'm getting a type mismatch. is it because Im defining two different last row's?

No, it's because you have changed the loop from a For Each, to a For Next.

 

[Guzzlr]

No, it's because you have changed the loop from a For Each, to a For Next.

'm definately doing something all wrong.
Instead of using a "Range" I'm trying to use the last row method beguinning at row 7 and going to the last row....and all I see is red!
I've defined last row, and I'm saying CL = 7 to the last row, and then moving into the Cl.value...
What am I missing, besides my sanity?
Thanks for the help

LastRow = Range("C:E").Cells.Find("*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox " The Last Non-Blank Row Is " & LastRow



'***************** Find ACI Group ******************
With Sheets("Template")
LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
cl = 7 to last row
For each cl
   cl.Value = Split(cl.Value, " /")(0)
Next cl
End With