Allow Uppercase Entries Only in Excel

Подписаться 106 тыс.

Просмотров 40 тыс.

50% 1

In this video, we use a custom formula as a Data Validation rule to allow uppercase entries only.
In this example, we want to control the input of UK postcodes. So we need to allow the entry of text and numbers, but the text must be uppercase.
We use a formula with the EXACT and UPPER functions to create this validation rule.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

Хобби

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 34

@leroyguillot481 5 лет назад

Heck yeah, I can think of several ways to use this really out of the box method. I love it!! Thanks

@Computergaga 5 лет назад

My pleasure Leroy. Thank you.

@ayeshanasir9916 6 лет назад

You are awesome computer gaga! Thank you!!

@Computergaga 6 лет назад

Thank you Ayesha. Very kind.

@yuli5023 6 лет назад

Great tutorial, well explained!

@Computergaga 6 лет назад

You're welcome Yu, thank you.

@SyedMuzammilMahasanShahi 6 лет назад

Thank yo mate for such an amazing video.

@Computergaga 6 лет назад

You're welcome Syed, thank you.

@mohdarifsiddiqui7647 Год назад

Very Well and Clear Explaination..Thanks a lot

@Computergaga Год назад

You're welcome. Thank you for your comment.

@ismailismaili0071 6 лет назад

Thank u so much awesome trick

@Computergaga 6 лет назад

Thank you Ismail.

@rajuar1955 6 лет назад

You are super genius ..

@Computergaga 6 лет назад

Thanks Raju :)

@imranali-iy5wk 6 лет назад

very good trick

@Computergaga 6 лет назад

Thank you Imran.

@shalinsasi 4 года назад

Thanks

@Computergaga 4 года назад

My pleasure Shalin.

@lenac3587 6 лет назад

Personally, for the sake of efficiency rather than force the user to enter in uppercase, I would turn all lowercase into uppercase but I guess that would be considered as a different tutorial ;)

@Computergaga 6 лет назад

Sure. And this can be achieved by using custom formatting on a range, or by using the UPPER function in a different column.

@lenac3587 6 лет назад

Hi Alan, thanks for responding. I'm thinking a long the lines of transforming the typed values into uppercase on the same cell rather than having it as an upper() function to appear on another column. If you know what I mean. So in this case perhaps it needs to be manipulate in vba. I was looking for a solution to solve a problem I have with a data validation list for Y/N values whereby if the user type 'y' in small it automatically prompts an error message which is annoying. So I wanted to turn it into a upper Y irregardless of what case is being used. So far I haven't found a tutorial for this yet.

@Computergaga 6 лет назад

Definitely can be done in VBA. You can have the macro run on the worksheet change event, or maybe when the workbook is saved. The code could use the StrConv function or UCase function to convert the changed cell(s) to uppercase automatically.

@Ruppercik 5 лет назад

Hmm, not working for me... When I'm typing =upper(A2) in a empty cell (eg. C3), hit Enter - I'm receiving a problem with name...

@meganathanve444 4 года назад

Effective solution sir..,,but allows Number entry..,,I Tried this one but =IFERROR(SUMPRODUCT((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=65)),0)=LEN(A2) Help me to shorten the function to only allow Capital letters..,,

@imranali-iy5wk 6 лет назад

can you please tell me how to solve insert row and column problem how to fix it?

@Computergaga 6 лет назад

Sorry Imran I don't know what you mean.

@imranali-iy5wk 6 лет назад

+Computergaga when I insert column in excel sheet it gives error how to fix this error?

@Computergaga 6 лет назад

What error do you get? You should not get errors when columns or rows are inserted. Are these formula errors you speak of?

@alirezamogharabi8733 6 лет назад

Thanks, I have a question! how can i extreac only numeric word from a complex text in a cell like this: abcd 1234 ddsadh dhdhdb

@Computergaga 6 лет назад

I have a video on separating text and numbers here - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kKlw6S36TF4.html It is a tricky one. If it is always the second group like your example. A simpler formula can be used or a tool name Flash Fill. Check it out if you don't know that one. It is awesome.

@alirezamogharabi8733 6 лет назад

Computergaga Thank you very much.

@alirezamogharabi8733 6 лет назад

Computergaga I have used The flash fill tool but it doesn't recognize the word that I want.

@Computergaga 6 лет назад

Damn. It was just a shot. Can be so useful.