How To Substitute with different length text

[sssyyy2002]

Hi There,

I need change Company Name with XXX, the rule is First word can show full, but start from second word, only first letter show it, the rest are show as "XXX".

For example, ABC Company --> ABC CXXXX; ABC SUSAN COMPANY-->ABC SXXXX CXXXXXXX (The number of "XXX" no need same as the original number of letters.)

The question is, since the length of Company name are very different, and the number of space (" ") are different too, how can I use substitute function to solve it?

Thank you all!

[XLent]

with your version (XL2013) this would be quite convoluted -- you would be better off using a Custom Function (stored in a standard Module); one basic approach:

Please Login or Register  to view this content.

which you could then call from your cell

Please Login or Register  to view this content.

formula wise... for 2013, accounting for up to 4 words:

Please Login or Register  to view this content.

edit: formula wise - if you don't care how many Xs appear (say 4) then

Please Login or Register  to view this content.

the same can be achieved in the UDF by modifying the String function to use 4 rather than leverage the length of the string being masked

[sssyyy2002]

Thank you very much. You save my life. Really appericate, Thanks again!!!!