Deleting Portions of Data within a Cell

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
943
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
with the value in A1 put formula in B1 and copy down

=TRIM(LEFT(A1,FIND("/",A1)-1))
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top