Using VBA to return data before the last hyphen

mrpavlos · Jun 25, 2019

Hello all

Thanks again for this great forum. It is such a great learning curve. I am wondering if somebody could help me with a query.

The BI dept at work send me on a weekly basis a report of SKU IDs and total qtys, but with the SKU description added to the SKU ID by a hypen. Some SKU IDs themselves do have a hyphen. I have included below an example table showing the IDs in Column A and the qtys in Column B. What I would like to have in Column C is the SKUID without the description (that is all the data before the hyphen) but in the case where the SKU itself would have a hyphen (i.e the data in Column A will show with two hyphens) I need the string before the second hyphen. The amount of SKUids and qtys in the respective columns changes from week to week and is not fixed.

In other words, if the SKUId in column A is showing with two hyphens return in Column C all the string before the second hyphen, if showing with one hyphen then return then just the data before that first hyphen. I know how to use a formula to return data before one hyphen but I am not sure in the case of when the column may have both one and two hyphens Id. Would it be possible to use a VBA solution that would populate Column C with the required SKU Ids and also populate Column D with the qtys. Please see the example table below which show in Column C and Column D the required results. Many thanks again

[TABLE="class: MsoNormalTable, width: 446"]
<tbody>[TR]
[TD="width: 243"]

SKU

[/TD]
[TD="width: 69"]

Qty

[/TD]
[TD="width: 172"]

SKU

[/TD]
[TD="width: 111"]

Qty

[/TD]
[/TR]
[TR]
[TD="width: 243"]

A545-DescA

[/TD]
[TD="width: 69"]

45

[/TD]
[TD="width: 172"]

A545

[/TD]
[TD="width: 111"]

45

[/TD]
[/TR]
[TR]
[TD="width: 243"]

tyty-DescB

[/TD]
[TD="width: 69"]

67

[/TD]
[TD="width: 172"]

tyty

[/TD]
[TD="width: 111"]

67

[/TD]
[/TR]
[TR]
[TD="width: 243"]

A654-DABTU-DescC

[/TD]
[TD="width: 69"]

43

[/TD]
[TD="width: 172"]

A654-DABTU

[/TD]
[TD="width: 111"]

43

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 330"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]

<colgroup><col style="mso-width-source:userset;mso-width-alt:5851;width:120pt" width="160"> <col style="mso-width-source:userset;mso-width-alt:1645;width:34pt" width="45"> <col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> </colgroup><tbody>
[TD="class: xl66, width: 73"][/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Fluff · Jun 25, 2019

You can do that without VBA

Excel 2013/2016

A

B

C

D

1

SKU

Qty

SKU

Qty

2

A545-DescA

45

A545

DescA

3

tyty-DescB

67

tyty

DescB

4

A654-DABTU-DescC

43

A654-DABTU

DescC

VBAinputs

Cell Formulas
Range	Formula
C2	=TRIM(LEFT(SUBSTITUTE(A2,"-",REPT(" ",500),LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))),500))
D2	=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",500)),500))

JoeMo · Jun 25, 2019

Your thread title requested a VBA solution. If that's what you want try this:

Code:

Sub GetSKU()
'assumes first SKU header is in A1 and first extract SKU starts in C2
Dim R As Range, Vin As Variant, Vout As Variant, i As Long
Set R = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Vin = R.Value
ReDim Vout(1 To UBound(Vin, 1), 1 To 1)
For i = 1 To UBound(Vin, 1)
    If InStr(Vin(i, 1), "-") > 0 Then
        ct = ct + 1
        Vout(ct, 1) = Mid(Vin(i, 1), 1, InStrRev(Vin(i, 1), "-") - 1)
    End If
Next i
Application.ScreenUpdating = False
Range("C2:C" & ct + 1).Value = Vout
Application.ScreenUpdating = True
End Sub

mrpavlos · Jun 25, 2019

Thank you Fluff for the formulas, and thank you JoeMo for the VBA solution. I really appreciate you both taking the time to read and help me with a solution. I have learnt from both of your approaches as both work great. Thanks again

MoshiM · Jun 25, 2019

I have one too !!!

Code:

Sub GG()

Application.screenupdating=false

Dim Original_String As String, Str_A, Final_String As String, X As Long, M As Long, K As Long
M = ActiveSheet.UsedRange.Rows.Count
For K = 2 To M
    Original_String = ActiveSheet.Range("A" & K).value2   
    Str_A = Split(Original_String, "-")    
    For X = LBound(Str_A) To UBound(Str_A) - 1    
        Final_String = Final_String & "-" & Str_A(X)        
    Next X    
    ActiveSheet.Range("C" & K).value2 = Final_String
    ActiveSheet.Range("D" & K).value2 = Str_A(UBound(Str_A))
Next K

application.screenupdating=true
End Sub

Fluff · Jun 25, 2019

You're welcome & thanks for the feedback

mrpavlos · Jun 25, 2019

Hello MoshiM

Thanks so much for sharing your solution. I really appreciate it. Column D gave the separation for the right side of the last hyphen fine, but Column C didnt appear to render Column A as desired. Please see below how Column C turned out.

[TABLE="width: 588"]
<tbody>[TR]
[TD]SKU
[/TD]
[TD]Qty
[/TD]
[TD]SKU
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]A545-DescA
[/TD]
[TD]45
[/TD]
[TD]-A545
[/TD]
[TD]DescA
[/TD]
[/TR]
[TR]
[TD]tyty-DescB
[/TD]
[TD]67
[/TD]
[TD]-A545-tyty
[/TD]
[TD]DescB
[/TD]
[/TR]
[TR]
[TD]A654-DABTU-DescC
[/TD]
[TD]43
[/TD]
[TD]-A545-tyty-A654-DABTU
[/TD]
[TD]DescC
[/TD]
[/TR]
</tbody>[/TABLE]

MoshiM · Jun 25, 2019

What about this?

Code:

Sub GG()


Application.ScreenUpdating = False


Dim Original_String As String, Str_A, Final_String As String, X As Long, M As Long, K As Long


M = ActiveSheet.UsedRange.Rows.Count


For K = 2 To M


    Original_String = ActiveSheet.Range("A" & K).value2
    
    Str_A = Split(Original_String, "-")
    
    For X = LBound(Str_A) To UBound(Str_A) - 1
    
        Final_String = Final_String & "-" & Str_A(X)
        
    Next X
    Final_String = Replace(Final_String, "-", "", 1, 1)
    ActiveSheet.Range("C" & K).value2 = Final_String:Final_String=[COLOR=#303336][FONT=inherit]vbNullString[/FONT][/COLOR]
    ActiveSheet.Range("D" & K).value2 = Str_A(UBound(Str_A))
     


Next K
Application.ScreenUpdating = True
End Sub

Fluff · Jun 25, 2019

@MoshiM
You will also need to set the string back to "" otherwise you will just end up adding to it.

mrpavlos · Jun 25, 2019

Thank you MoshiM

The revised VBA solution worked fine for me. Many thanks again!

Using VBA to return data before the last hyphen

mrpavlos

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

JoeMo

MrExcel MVP

mrpavlos

New Member

MoshiM

Active Member

Fluff

MrExcel MVP, Moderator

mrpavlos

New Member

MoshiM

Active Member

Fluff

MrExcel MVP, Moderator

mrpavlos

New Member

Similar threads

Forum statistics

Share this page

Using VBA to return data before the last hyphen

New Member

Excel Facts

MrExcel MVP, Moderator

MrExcel MVP

New Member

Active Member

MrExcel MVP, Moderator

New Member

Active Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock