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]
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