0

I have a single excel cell that contains the following data:

0.5 0.6, 0.3 0.4, 0.8 0.9

Is there a formula I can apply that removes everything from the cell that is not before the first comma and after the last comma. My wanted result would be the following:

0.5 0.6, 0.8 0.9
user3200392
  • 617
  • 1
  • 8
  • 20
  • You say "first" and "last" commas. Does that indicate that the number of commas is not fixed (at 2)? – XOR LX May 14 '15 at 13:47
  • yes, the number of commas can vary – user3200392 May 14 '15 at 13:49
  • You can use VBA to grab the cell contents as a string then split it on the commas. Next, recombine the data using the first and last elements of the array you got from the split. This will accommodate any number of commas as long as there is at least one. – Lance May 14 '15 at 13:50
  • Similar to: http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba – xQbert May 14 '15 at 13:55

2 Answers2

2

Try:

=LEFT(A1,FIND(",",A1)+1)&TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))

Regards

XOR LX
  • 7,627
  • 1
  • 15
  • 15
0

With data in A1, in B1 enter:

=LEFT(A1,7) & ", " &RIGHT(A1,7)

enter image description here

Gary's Student
  • 94,018
  • 8
  • 54
  • 89