Calculate content of a cell in excel

[Kevin Neufeld]

I have a table below. Column A represents the shipments that are consolidated into one single load. Is there a formula I can put in column B to automatically figure the number of shipments? each shipment is separated by a comma.

thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Shipment Number[/TD]
[TD]Total Shipments[/TD]
[/TR]
[TR]
[TD]SHP001802820, SHP001803122, SHP001803362, SHP001803324, SHP001803491, SHP001803565, SHP001803602, SHP001803569, SHP001803613[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]SHP001808611[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]SHP001813687, SHP001814004, SHP001814257, SHP001814731[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]SHP001803965, SHP001807921[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[SyedUsman]

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

Assuming your shipment is in cell A2

 

[Kevin Neufeld]

That worked perfectly, thank you very much.