Drop everything after character ($)

[BrutalDawg]

I am still to find a one time solution to drop everything in column B that comes after the $.

For example, all of column be will have items such as abc123$$45@@ceb, def456$$25@@cab. I want to setup a rule where everything in column B would be limitted to abc123, def456, etc.

Currently I can just run a find a replace, or use the trim or left functions to put the content in another cell, but I am trying to find a way that I can apply this to the whole B column.

Currently the user is using a scanner to insert the data into column B, but the barcode contains additional information after the $ that is not required for this exercise.

Any ideas?

Current		Desired
Date	Item	Quantity		Date	Item	Quantity
5/25/2021	3103602$$50	50(50)		44341​	3103602​	50(50)
5/25/2021	DZ114363$$3	3(3)		44341​	DZ114363	3(3)
5/25/2021	DZ114364$$3	3(3)		44341​	DZ114364	3(3)
5/25/2021	DZ114367$$3	3(3)		44341​	DZ114367	3(3)
5/25/2021	DZ114369$$3	3(3)		44341​	DZ114369	3(3)

As you can see from the example, our barcode contains itemnumber$$quantity, i would only like the item quantity in column B, ideally it could move quantity to column c but that is not required.

 

[Joe4]

How about a VBA solution that drops the string after the "$" as values are being entered into column B?
Would that be an acceptable solution?

 

[maabadi]

If you want result at same cell, you should use macro (Vba)

 

[Joe4]

If my approach is acceptable, to control it as the data is scanned/entered, then right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arr() As String

    If Target.CountLarge > 1 Then Exit Sub
    
    If Target.Column = 2 Then
        Application.EnableEvents = False
        arr = Split(Target.Value, "$")
        Target.NumberFormat = "@"
        Target = arr(0)
        Application.EnableEvents = True
    End If

End Sub

 

[BrutalDawg]

Hello Joe,

You solution works great! Exactly what I was looking for.

One issue I am seeing now, is there is no VBA in an shared excel sheet because it is actually excel online, but I think with this just being a single user he should still be able to use it from his desktop app and share. Will update back.

 

[Joe4]

One issue I am seeing now, is there is no VBA in an shared excel sheet because it is actually excel online, but I think with this just being a single user he should still be able to use it from his desktop app and share. Will update back.

Yes. VBA should be operational if the download it to their desktop.

Just note that any solution that you want to handle it right in column B (without having to add new columns or do stuff manually) will require VBA.