data validation have

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data Validation - have I broken Excel? It has mixed views on the

Data Validation - have I broken Excel? It has mixed views on the

resolvedResolved · Low Priority · Version 365

Martin has attended:
Excel Advanced course

Data Validation - have I broken Excel? It has mixed views on the

Hi - filled with enthusiasm after yesterday's Advanced Excel course with Jens, I returned to the office determined to control data entry on a particular spreadsheet.

Please note below, when I type ", I show that as the delimiter for text, and the " is not part of the text:

I have found a weakness already: on the header record for a table, if you click the down arrow, you can see the values recorded in that column. However, it won't show "PASSPORTS " (notice the space character after) as a separate value to "PASSPORTS" (no space).

And yet, if I create a SUMIFS formula summing costs where category is "PASSPORTS" it won't include records with "PASSPORTS " as being in the category. So one part of Excel thinks they are the same category and another doesn't.

Finding this out has taken considerable time, so I thought "let's create some Data Validation rules to limit data entry! However, when I create a list including "PASSPORTS", and not "PASSPORTS ", Excel allows either to be entered, ruining my validation rule.
My question is: is there a way to restrict space characters so that they are not allowed as an extra after a text string?

I hope the answer is easier to read than my question!

RE: Data Validation - have I broken Excel? It has mixed views on

Hi Martin,

Thank you for the forum question. Good you enjoyed the course yesterday.

Spaces are a nightmare in Excel. A space is a character and yes it is strange that the header filter doesn't see a space as a character.

I cannot understand if Excel allow you to put a space after PASSPORT if you in the validation list do not have a space.

Anyway you have different ways of solving the issue.

You have the TRIM function which will remove unwanted spaces, Power Query can do it, but you can also work with wildcards.

I have attached an Excel file to my answer. If you need to SUMIFS something look at my example with wildcard "*".

You will also find a TRIM function example in the file.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

Passport space.xlsx

Fri 24 Mar 2023: Automatically marked as resolved.


 

Excel tip:

Showing all menu items

If you go into a pull down menu you usally find that you get a selection of items(this is the default) or sometimes everything. If you only get a selectiopn you have to go to the double arrows at the bottom of the menu and click it to get all of the hidden items.
To turn this off so that you always get everything, go to Tools / Customize / Options Tab / "Always show full menus". Make sure there is a tick in the box and you will always have every item.

View all Excel hints and tips


Server loaded in 0.58 secs.