Remove any letters except the capital letters

[mactrading]

Hello guys!

How to Remove any letters except the capital letters from a group of numbers.

So the "#", "Capital Letters", and the numbers are retained. All small letters at the end are removed.



Thanks in advance

[WideBoyDixon]

How about this:

Please Login or Register  to view this content.

WBD

[mehmetcik]

B2 ==let(a,substitute(substitute(a2," ",""),char(9),""),if(isnumber(right(a,1)*1),a,left(a,len(a)-1)))

[DJunqueira]

And what if you want to remove small caps from the middle of the text?

[DJunqueira]

And what if you want to remove small caps from the middle of the text?

My answer to myself.

Please Login or Register  to view this content.

[Bo_Ry]

Please try

Keep upper case
=LET(z,CLEAN(A2),m,MID(z,SEQUENCE(LEN(z)),1),CONCAT(REPT(m,EXACT(m,UPPER(m)))))

Keep lower case
=LET(z,CLEAN(A2),m,MID(z,SEQUENCE(LEN(z)),1),CONCAT(REPT(m,EXACT(m,LOWER(m)))))

[DJunqueira]

Please try
Keep upper case
=LET(z,CLEAN(A2),m,MID(z,SEQUENCE(LEN(z)),1),CONCAT(REPT(m,EXACT(m,UPPER(m)))))
Keep lower case
=LET(z,CLEAN(A2),m,MID(z,SEQUENCE(LEN(z)),1),CONCAT(REPT(m,EXACT(m,LOWER(m)))))

No Insider for me (Win7), but I saved it.

[mactrading]

Thank you

[mactrading]

Hi,

Thank you so so much

[mactrading]

How about this:

Please Login or Register  to view this content.

WBD

Thank you so much

[mactrading]

B2 ==let(a,substitute(substitute(a2," ",""),char(9),""),if(isnumber(right(a,1)*1),a,left(a,len(a)-1)))

Thank you so much

[mactrading]

No Insider for me (Win7), but I saved it.

Thank you so much

[mactrading]

hi DJunqueira,

hehehe, its ok only need to remove the small letter at the end.

Thank you so so much

[DJunqueira]

I know, it was a bonus.

[mactrading]

Please try

Keep upper case
=LET(z,CLEAN(A2),m,MID(z,SEQUENCE(LEN(z)),1),CONCAT(REPT(m,EXACT(m,UPPER(m)))))

Keep lower case
=LET(z,CLEAN(A2),m,MID(z,SEQUENCE(LEN(z)),1),CONCAT(REPT(m,EXACT(m,LOWER(m)))))

Wow, all the answers are really amazing!!! Thank you

[mactrading]

I know, it was a bonus.

Thank you Thank you

[mactrading]

B2 ==let(a,substitute(substitute(a2," ",""),char(9),""),if(isnumber(right(a,1)*1),a,left(a,len(a)-1)))

Hello Thank you

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.