TRIM Formula - Remove Comma from value in cell

[hrayani]

Hi All,

I need a formula to remove commas from a cell...

Sample Data

123, 456, 567,,,,,, required answer 123, 456, 567
990,256,, reqiured answer 990,256
12567, 308211, required answer 12567, 308211

I want all commas to be removed which are at the end of the cell value

Any help would be appreciated

Regards,

Humayun

 

[sandy666]

maybe:

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",", ")

 

[hrayani]

Hi Sandy,

Thanks for the reply

There is one issue i.e. there are some cell containing value like below (sorry i should have mentioned it in the first post)

[TABLE="width: 364"]
<tbody>[TR]
[TD="class: xl65, width: 364"]14778971 A, 14778971 B, , , ,

Answer should be ----> 14778971 A, 14778971 B [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 364"]
<tbody>[TR]
[TD="class: xl65, width: 364"]whereas, it is showing 14778971, A, 14778971, B[/TD]
[/TR]
</tbody>[/TABLE]

Any Idea ???

 

[steve the fish]

This should do it:

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,", ,",",,")&",,",",,",REPT(" ",99)),99))

 

[hrayani]

Hi Steve,

Thanks problem solved.... formula you provided is working Perfect...


Just wanted to ask you that will the formula work for any number of commas at the end of the cell or is there a limit to it ??

 

[steve the fish]

Any number of commas will be fine. It wont work however if there are pairs of commas in the middle of the string.

 

[hrayani]

Thanks one last thing... for a range of cells

Example...

A1 = 256
A2 = Humayun
A3 = Abc

Required Answer in A4 = 256, Humayun, Abc

Like combine/concatenate all the cells in the range with commas....

 

[steve the fish]

Textjoin if you have it:

=TEXTJOIN(",",TRUE,A1:A3)

or if not concatenate.

=CONCATENATE(A1,",",A2,",",A3)

 

[hrayani]

Hi Steve,

Textjoin not working...

Concatenate is fine but for that all the cells in the range will have to have some values in there...

I also realise that i should start a new thread for this one as the formula i need now is a whole new story altogether.

 

[steve the fish]

Could write a UDF if you wanted. Depends if you want to use code.