Macro to delete everything after occurence of second comma in a Range

naira · Dec 23rd 2014

Hi!

I have data in multiple rows and columns that looks as below, i.e., group of nos. and ratios separated by commas:

[TABLE="width: 578"]

[tr]

[TD="align: center"]USA
[/TD]
[TD="align: center"]CANADA
[/TD]
[TD="align: center"]RUSSIA
[/TD]

[/tr]

[tr]

[td]

39008,29396,1.327 - 1,

[/td]

[td]

10056,17020,1 - 1.693,95

[/td]

[td]

28829,43751,1 - 1.518,89

[/td]

[/tr]

[tr]

[td]

4380,6085,1 - 1.389,

[/td]

[td]

25805,24920,1.036 - 1,

[/td]

[td]

5259,9780,1 - 1.860,

[/td]

[/tr]

[tr]

[td]

5771,7304,1 - 1.266,

[/td]

[td]

30066,30074,1 - 1.000,20

[/td]

[td]

13026,17561,1 - 1.348,

[/td]

[/tr]

[tr]

[td]

6271,6129,1.023 - 1,

[/td]

[td]

29012,33676,1 - 1.161,27

[/td]

[td]

10829,16963,1 - 1.566,

[/td]

[/tr]

[tr]

[td]

6268,6129,1.023 - 1,84

[/td]

[td]

29071,34456,1 - 1.185,40

[/td]

[td]

16569,22161,1 - 1.337,

[/td]

[/tr]

[tr]

[td]

6268,6229,1.006 - 1,83

[/td]

[td]

28213,34609,1 - 1.227,45

[/td]

[td]

13003,19053,1 - 1.465,

[/td]

[/tr]

[tr]

[td]

6463,6223,1.039 - 1,55

[/td]

[td]

28800,33519,1 - 1.164,44

[/td]

[td]

12693,20713,1 - 1.632,

[/td]

[/tr]

[tr]

[td]

6464,6836,1 - 1.058,

[/td]

[td]

28961,34922,1 - 1.206,46

[/td]

[td]

15448,22052,1 - 1.427,62

[/td]

[/tr]

[tr]

[td]

7064,7646,1 - 1.082,

[/td]

[td]

30333,37335,1 - 1.231,53

[/td]

[td]

20819,28523,1 - 1.370,63

[/td]

[/tr]

[tr]

[td]

6995,8277,1 - 1.183,

[/td]

[td]

15386,37116,1 - 2.412,88

[/td]

[td]

20894,30302,1 - 1.450,67

[/td]

[/tr]

[tr]

[td]

6995,8457,1 - 1.209,

[/td]

[td]

15674,35386,1 - 2.258,89

[/td]

[td]

21594,29997,1 - 1.389,63

[/td]

[/tr]

[tr]

[td]

7201,8460,1 - 1.175,

[/td]

[td]

15542,38347,1 - 2.467,95

[/td]

[td]

21161,30614,1 - 1.447,69

[/td]

[/tr]

[/TABLE]

What I need is a macro (or a formula) that deletes everything after the occurrence of the second comma in each cell, including the second comma also.

So my new data should look like this:

[TABLE="width: 578"]

[tr]

[td]

USA

[/td]

[td]

CANADA

[/td]

[td]

RUSSIA

[/td]

[/tr]

[tr]

[td]

39008,29396

[/td]

[td]

10056,17020

[/td]

[td]

28829,43751

[/td]

[/tr]

[tr]

[td]

4380,6085

[/td]

[td]

25805,24920

[/td]

[td]

5259,9780

[/td]

[/tr]

[tr]

[td]

5771,7304

[/td]

[td]

30066,30074

[/td]

[td]

13026,17561

[/td]

[/tr]

[tr]

[td]

6271,6129

[/td]

[td]

29012,33676

[/td]

[td]

10829,16963

[/td]

[/tr]

[tr]

[td]

6268,6129

[/td]

[td]

29071,34456

[/td]

[td]

16569,22161

[/td]

[/tr]

[tr]

[td]

6268,6229

[/td]

[td]

28213,34609

[/td]

[td]

13003,19053

[/td]

[/tr]

[tr]

[td]

6463,6223

[/td]

[td]

28800,33519

[/td]

[td]

12693,20713

[/td]

[/tr]

[tr]

[td]

6464,6836

[/td]

[td]

28961,34922

[/td]

[td]

15448,22052

[/td]

[/tr]

[tr]

[td]

7064,7646

[/td]

[td]

30333,37335

[/td]

[td]

20819,28523

[/td]

[/tr]

[tr]

[td]

6995,8277

[/td]

[td]

15386,37116

[/td]

[td]

20894,30302

[/td]

[/tr]

[tr]

[td]

6995,8457

[/td]

[td]

15674,35386

[/td]

[td]

21594,29997

[/td]

[/tr]

[tr]

[td]

7201,8460

[/td]

[td]

15542,38347

[/td]

[td]

21161,30614

[/td]

[/tr]

[/TABLE]

Please note that the macro will need to be run on a range of about 275 columns and 1500 rows.
If the solution is a formula, I can always use it on another sheet by looking at the data range on the first sheet.

Thanx,

Naira

**S O** · Dec 23rd 2014

Re: Macro to delete everything after occurence of second comma in a Range

Try this formula:

=LEFT(A1,SEARCH(",",A1,SEARCH(",",A1)+1)-1)

**KjBox** · Dec 23rd 2014

Re: Macro to delete everything after occurence of second comma in a Range

Or, using a button on the sheet and assigning this macro to it

Code

Sub NewValues()


Dim x, y, i As Long, ii As Long


    With Cells(1, 1).CurrentRegion.Offset(1).Resize(Cells(1).CurrentRegion.Rows.Count - 1)
        x = .Value
        For i = 1 To UBound(x, 1)
            For ii = 1 To UBound(x, 2)
                If x(i, ii) <> "" Then y = Split(x(i, ii), ","): x(i, ii) = y(0) & "," & y(1)
            Next
        Next
        .NumberFormat = "@"
        .Value = x
    End With


End Sub

Display More

Assumes Row 1 is header row and table starts in Column A.

jindon · Dec 24th 2014

Re: Macro to delete everything after occurence of second comma in a Range

This should do

Code

Sub test()
    With ActiveSheet.UsedRange
        .NumberFormat = "@"
        .Value = Evaluate("if(" & .Address & "<>"""",if(len(" & .Address & ")-len(substitute(" & _
        .Address & ","","",""""))>2,left(" & .Address & ",find(""^^"",substitute(" & .Address & _
        ","","",""^^"",2))-1)," & .Address & "),"""")")
    End With
End Sub

naira · Dec 24th 2014

Re: Macro to delete everything after occurence of second comma in a Range

Hi Guys,

Thanks a lot for your replies.

1. S O's formula works great
2. KjBox's solution gives me a subscript out of range error
3. jindon's solution works great too

Thread closed.

Macro to delete everything after occurence of second comma in a Range

Participate now!

Share