This is better. =SUBSTITUTE(A1," ","")

Epinn

"Epinn" wrote in message ...
If we want to remove trailing spaces, this is one way to do it.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Max, feel free to correct me if I am wrong.

Epinn

"Epinn" wrote in message ...
I have one concern.

Part no. ABC123
and
Part no. ABC123___

may be counted twice because of the trailing spaces in the latter??

Max, in that case, we have to do LEN ( ), SUBSTITUTE ( ) etc., right?

Epinn

"Epinn" wrote in message ...
Max,



Can the poster use the following? I learned the COUNTIF formula from the experts.



If the part numbers are strictly numbers, then FREQUENCY ( ) can be used.



=SUMPRODUCT(--(FREQUENCY(A1:A55000,A1:A55000)0))



If the part numbers are text, numbers or a combination of both, then use COUNTIF ( ).



=SUMPRODUCT((A1:A55000<"")/COUNTIF(A1:A55000,A1:A55000&""))



Blanks will not be counted in both cases.



Epinn



"Tiziano" wrote in message ...
Max,
I would like to add some sort of formula (separate from the formulas you
gave me before) that lets me know how many unique part numbers I have in
column A of my spreadsheet.
Can you help?
Thanks.
--
Tiziano

"Max" wrote in message
...
Tiziano, you're welcome. Let me know how it went for you. As mentioned in
my response to Epinn, due to the inherent calc-intensiveness here, it's
advisable to set the book's calc mode to Manual first. Then do the set-up.
And then press F9 to recalc whenever required.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote in message
...
Thanks, I will try it out and see what happens.
--
Tiziano