Deleting Portions of Data within a Cell

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,705
Office Version
  1. 365
Platform
  1. Windows
with the value in A1 put formula in B1 and copy down

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

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
759
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

New Member
Joined
Sep 26, 2018
Messages
8
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If there is always a space before the / try
Code:
Sub GetLeft()
Dim cl As Range
For Each cl In Range("A2:A500")
   cl.Value = Split(cl.Value, " /")(0)
Next cl
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,705
Office Version
  1. 365
Platform
  1. Windows
call function provided by @ldonkers like this

to put the value in next column
Code:
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
Code:
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

Well-known Member
Joined
Apr 20, 2009
Messages
759

ADVERTISEMENT

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

Well-known Member
Joined
Apr 20, 2009
Messages
759
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 20, 2009
Messages
759
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,618
Messages
5,523,935
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top