Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Data Validation Must Contains mark ' (approstrops) in Left

  1. #1
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Data Validation Must Contains mark ' (approstrops) in Left

    hi all,

    i need your help how create data validation with criteria :
    - start mark ' (approstrops) in left position like example this below :

    '6136716
    '981289
    '2091234
    etc...

    i have using formula =LEFT(B3)="'", but still not working...

    any help me, thanks in advance...

    m.susanto

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,284
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    Not sure I understand your entire issue. However, the syntax for the left function is =Left(text,#characters). So you are missing the number of characters in your function
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    https://www.coursehero.com/file/8261...atabaseDesign/



  3. #3
    Board Regular
    Join Date
    Jan 2009
    Location
    Hong Kong
    Posts
    791
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    apparently, you want number to be input as text. You don't need data validation to achieve that. Just change the format of the cells (before input) to text.


    Quote Originally Posted by muhammad susanto View Post
    hi all,

    i need your help how create data validation with criteria :
    - start mark ' (approstrops) in left position like example this below :

    '6136716
    '981289
    '2091234
    etc...


    m.susanto
    A simple spreadsheet you can manage could be a better choice than a sophisticated spreadsheet you need help from time to time.
    http://wmfexcel.com/welcome/

  4. #4
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    thanks guys, i means i want to input data with criteria start mark ' if not the data ignore...

  5. #5
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    if the inputting data not contains mark ' (in left position) so ignore..
    how to make it (formula) with "custom" in Data Validation feature...

  6. #6
    Board Regular
    Join Date
    Jan 2009
    Location
    Hong Kong
    Posts
    791
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    I have no solution for you...
    but may i know why you need this? What's the reason behind?


    Quote Originally Posted by muhammad susanto View Post
    if the inputting data not contains mark ' (in left position) so ignore..
    how to make it (formula) with "custom" in Data Validation feature...
    A simple spreadsheet you can manage could be a better choice than a sophisticated spreadsheet you need help from time to time.
    http://wmfexcel.com/welcome/

  7. #7
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    thanks m fexcel, i confused when i use mark " not ' my formula is correct..

    =LEFT(B8)=""" but if i i replace mark " with mark '

    for detail :
    =LEFT(B8)="""------ the result is correct
    =LEFT(B8)="'"------- the result is wrong

    how do solve?

  8. #8
    Board Regular
    Join Date
    Jan 2009
    Location
    Hong Kong
    Posts
    791
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    but putting ' in front of any input basically tells Excel that you are going to input text. Excel doesn't trick the ' in the front as a character. I wonder if there is a way to solve your problem.

    anyway, back to my question, why you need to restrict user to input with '?



    Quote Originally Posted by muhammad susanto View Post
    thanks m fexcel, i confused when i use mark " not ' my formula is correct..

    =LEFT(B8)=""" but if i i replace mark " with mark '

    for detail :
    =LEFT(B8)="""------ the result is correct
    =LEFT(B8)="'"------- the result is wrong

    how do solve?
    A simple spreadsheet you can manage could be a better choice than a sophisticated spreadsheet you need help from time to time.
    http://wmfexcel.com/welcome/

  9. #9
    Board Regular muhammad susanto's Avatar
    Join Date
    Jan 2013
    Location
    KPKNL Pekanbaru, Riau
    Posts
    1,344
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    hi m fexcel, i have formula this below

    inputting -------------after formula
    (=IF(--LEFT($B7;2)<20;20&$B7;19&$B7)

    9810316 --------------199810316 (result after formula above)
    '0006137--------------200006137 (result after formula above)

    without ' --------------196137 (wrong result)

    any ideas?

  10. #10
    Board Regular
    Join Date
    Jan 2009
    Location
    Hong Kong
    Posts
    791
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation Must Contains mark ' (approstrops) in Left

    I guess your problem is when someone input 0006137, Excel turns it into 6137 automatically and thus making your formula not work.

    My suggestion in post #3 should help. Have you tried that?

    Anyway, if all your input is 7-digit, and you insist on Data Validation, you may try the following D.V.
    Allow:
    Text Length
    Data:
    Equal to
    Length: 7

    then in the Error Alert, you may prompt the user to input ' beforehand IF the number they are going to input starts with 0

    Hope this helps.



    Quote Originally Posted by muhammad susanto View Post
    hi m fexcel, i have formula this below

    inputting -------------after formula
    (=IF(--LEFT($B7;2)<20;20&$B7;19&$B7)

    9810316 --------------199810316 (result after formula above)
    '0006137--------------200006137 (result after formula above)

    without ' --------------196137 (wrong result)

    any ideas?
    A simple spreadsheet you can manage could be a better choice than a sophisticated spreadsheet you need help from time to time.
    http://wmfexcel.com/welcome/

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •