• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation not working on recent smartphone inputs

D

Dermot

New Member
Hello Friends,

- I have recently observed an issue regarding my data in a column that I use to perform data validation on my spreadsheet.
- So There is nothing wrong with the formula, neither is there anything from with the use of data validation.
- It should be looking for duplicate entries, which works quite fine.
- The issue is that it no longer recognizes input made from a smartphone using the excel app.
- so what i did was to retype cell text field from my PC and it worked perfectly.

Is there a way that I can continue using this technique (Data validation) without having to re-enter data from a PC in order for it to process?

-in the Excel file the entry "Bennett peter jr" is not recognized in the data validation. I think this quite odd. It was an entry that was entered from a cell phone that i copied directly from the actual spreadsheet.

- if you try any other entry You will find that they work.
 

Attachments

  • DataValidation Test (Error with peter jr entry).xlsx
    10.6 KB · Views: 3
Dermot
Could Your 'Bennet peter jr' work better if You would take that one extra character away?
Check snapshot ... there is 'something' after letters tt.
Screenshot 2020-02-01 at 10.31.04.png
 
vletm said:
Dermot
Could Your 'Bennet peter jr' work better if You would take that one extra character away?
Check snapshot ... there is 'something' after letters tt.
View attachment 65305
Click to expand...

You know You could be right? I tried adding a trim() function to the formula in the test column and it worked for the "Bennett peter jr" Error case.

- So additional spaces could throw off the check. I was not thinking within those lines.

Thanks for the additional error case Vletm.

Thanks for giving it a look!!!
 

Attachments

  • DataValidation Test (Error with peter jr entry).xlsx
    10.7 KB · Views: 3
Dermot
It won't matter which extra character are there 'spaces' or other 'invisible' characters should take care as well as other visible characters.
Have You tested clean-function?
Try to avoid those 'extras'.
 
vletm said:
Dermot
It won't matter which extra character are there 'spaces' or other 'invisible' characters should take care as well as other visible characters.
Have You tested clean-function?
Try to avoid those 'extras'.
Click to expand...
I'm not sure what a clean-function is. What is that?
 
eg

CLEAN function - Microsoft Support

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data...
support.office.com support.office.com
 
vletm said:
eg

CLEAN function - Microsoft Support

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data...
support.office.com support.office.com
Click to expand...
OK thank you, vletm. I will also implement the Clean() Function.
 
You must log in or register to reply here.
Back
Top