Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment limiter la longueur des caractères dans une cellule dans Excel?

Parfois, vous souhaiterez peut-être limiter le nombre de caractères qu'un utilisateur peut entrer dans une cellule. Par exemple, vous souhaitez limiter jusqu'à 10 caractères peuvent être saisis dans une cellule. Ce tutoriel vous montrera les détails pour limiter les caractères dans la cellule dans Excel.


Limiter la longueur des caractères dans une cellule

1. Sélectionnez la plage dans laquelle vous allez limiter les entrées de date avec une longueur de caractère spécifiée.

2. Clique le La validation des données et Outils de données groupe sous Data languette.

3. Dans la boîte de dialogue Validation des données, sélectionnez le Longueur du texte article du Autoriser: boîte déroulante. Voir la capture d'écran suivante:

4. Dans le Dates: liste déroulante, vous aurez beaucoup de choix et en sélectionner un, voir la capture d'écran suivante:

(1) Si vous voulez que les autres ne puissent saisir que le nombre exact de caractères, dit 10 caractères, sélectionnez le égal à article.
(2) Si vous souhaitez que le nombre de caractères saisis ne dépasse pas 10, sélectionnez le moins que article.
(3) Si vous souhaitez que le nombre de caractères saisis ne soit pas inférieur à 10, sélectionnez le plus grand que article.

4. Numéro exact de l'entrée que vous souhaitez limiter Courant /Le minimum/Longueur box selon vos besoins.

5. Cliquez OK.

Désormais, les utilisateurs ne peuvent saisir que du texte avec un nombre limité de caractères dans les plages sélectionnées.

Empêchez facilement de taper des caractères spéciaux, des chiffres ou des lettres dans une cellule / sélection dans Excel

Avec Kutools for Excel's Empêcher la frappe fonctionnalité, vous pouvez facilement limiter les types de caractères dans une cellule ou une sélection dans Excel. Essai gratuit complet de 30 jours!
A. Empêcher de saisir des caractères spéciaux, tels que *, !, etc.;
B. Empêcher de taper certains caractères, tels que des chiffres ou certaines lettres;
C. Autorisez uniquement à taper certains caractères, tels que des chiffres, des lettres, etc. selon vos besoins.
ad empêcher de taper des caractères

Définir le message d'entrée pour la limitation de la longueur du texte

La validation des données nous permet de définir le message d'entrée pour la limitation de la longueur du texte en plus de la cellule sélectionnée, comme illustré ci-dessous:

1. Dans la boîte de dialogue Validation des données, basculez vers le Message de saisie languette.

2. Vérifier la Afficher le message d'entrée lorsque la cellule est sélectionnée option.

3. Saisissez le titre et le contenu du message.

4. Cliquez OK.

Revenez maintenant à la feuille de calcul et cliquez sur une cellule dans la plage sélectionnée avec limitation de la longueur du texte, il affiche une astuce avec le titre et le contenu du message. Voir la capture d'écran suivante:


Définir l'alerte d'erreur pour la limitation de la longueur du texte

Une autre façon de dire à l'utilisateur que la cellule est limitée par la longueur du texte consiste à définir une modification d'erreur. L'alerte d'erreur sera affichée après la saisie de données invalides. Voir la capture d'écran:

1. Dans la boîte de dialogue Validation des données, basculez vers le Alerte d'erreur boite de dialogue.

2. Vérifier la Afficher l'alerte d'erreur après la saisie de données non valides option.

3. Sélectionnez l' Attention, article du Style: boîte déroulante.

4. Saisissez le titre de l'alerte et le message d'alerte.

5. Cliquez OK.

Désormais, si le texte que vous avez entré dans une cellule n'est pas valide, par exemple, il contient plus de 10 caractères, une boîte de dialogue d'avertissement apparaîtra avec un titre et un message d'alerte prédéfinis. Voir la capture d'écran suivante:


Démo: limiter la longueur des caractères dans les cellules avec message d'entrée et avertissement d'alerte


Kutools pour Excel comprend plus de 300 outils pratiques pour Excel, gratuits à essayer sans limitation en 30 jours. Téléchargez et essayez gratuitement maintenant!

Un clic pour éviter d'entrer des données en double dans une seule colonne / liste

Comparé à la configuration de la validation des données un par un, Kutools for Excel's Empêcher la duplication L'utilitaire prend en charge les utilisateurs d'Excel pour empêcher les entrées en double dans une liste ou une colonne en un seul clic. Essai gratuit complet de 30 jours!
annonce empêcher la saisie de doublons


Article connexe:

Comment limiter les entrées de valeur de cellule dans Excel?


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    JJ · 1 years ago
    This is almost the exact solution I need but still need a bit more help. What I'm trying to achieve is to set a cell to have a max of 40 characters but not stop the user from entering all the data he needs, instead i would like anything over the 40 character limit to be populated in a second designated cell. Is this even a possibility? Thank you everyone in advance for any assistance provided.
    • To post as a guest, your comment is unpublished.
      Guest · 1 years ago
      I found this information useful to resolve a similar issue that I am facing.

      https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7
  • To post as a guest, your comment is unpublished.
    Michael · 1 years ago
    I saw Tomas question about putting an exact limit of 10 spaces and your formula =A1&REPT(" ",10-LEN(A1)) however I need to take it a step further. I want to take three separate fields that I have set their spaces to exactly 10 and concatenate them with their "spaces" intact. Also, if I don't limit what they enter and they enter MORE than 10 characters in one of the cells I want to take ONLY the first 10. So as an example, I want to give them three cells they can enter information in. I don't want to limit what they input BUT, I want to concatenate these three fields and pick up 10 characters from each one. So if the first cell has 4 characters, in my concatenate formula I want it to pick up the 4 characters PLUS 6 spaces. If the second field has 20 characters, I want to only pick up the first 10 characters and the same thing for the third cell. We are trying to get to a uniform 30 characters of description but some names are longer or shorter than others. We want this to break evenly with 10 characters per cell. Hopefully this is making sense.
  • To post as a guest, your comment is unpublished.
    Tomas · 2 years ago
    Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?
    I want to set a cell to 10 character, when input 2 character then will auto fill up with 8 space after. if the cell is blank, then return with 10 space. this is for setting a excel file for user input and save as txt or cvs file for import to other software.
    • To post as a guest, your comment is unpublished.
      kellytte · 1 years ago
      Hi Tomas,
      You can use a formula to limit the text length: =A1 & REPT(" ",10-LEN(A1))
  • To post as a guest, your comment is unpublished.
    question · 3 years ago
    I want to limit the quantity of a cell depending on the category of another one,

    For example if I input in A1 "Ok" in B1 must be limit to 10 characters
    but if A1= "NG", B1 must be limit to 12 characters.
  • To post as a guest, your comment is unpublished.
    Eliza · 4 years ago
    [quote name="Ivan"]Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?[/quote]
    I want to set a cell to 10 character, when input 2 character then will auto fill up with 8 space after. if the cell is blank, then return with 10 space. this is for setting a excel file for user input and save as txt or cvs file for import to other software
  • To post as a guest, your comment is unpublished.
    ARNAB DEBNATH · 4 years ago
    Hi,
    I have a attendance sheet. from 1 to 31. I put "P" on each cell if person is present.
    Now I want that how many times "P" is continuing present in cell.
    As as example - I have put "P" from 1 to 6 , then from 8th to 9th put P, and 10th is gap. then from 11th its continue to 18th. ...
    now i want how many times P is continue 6 time . PPPPPP PP PPPPPPPP
    Manually the answer is : 2(1to6 = 1,11to18=1)
    If you have any formula to count this it will be a great help.
    • To post as a guest, your comment is unpublished.
      Paul · 4 years ago
      =COUNTIF(B2:B17,">""") this formula will ignore empty cells but will count cells with data in i.e. P
  • To post as a guest, your comment is unpublished.
    Shawn · 5 years ago
    Hi. I want out put txt file and no spaces between cell values. Like 3 cells with First Name, Middle and last. Entered Shawn G Goldman as SHAWNGGOLDMAN
  • To post as a guest, your comment is unpublished.
    Shawn · 5 years ago
    Hi I want few things in a cell. I only want numbers in cell. I want to limit to 10 characters. I want to remove decimal like 15.00 to 1500. I want to indent to right. Also to add 0's to left to make it 10 characters .like 15.00 to 0000001500
  • To post as a guest, your comment is unpublished.
    Praveen · 5 years ago
    Sir, How to set Cells with following Condition
    If one letter will enter go to first cell and 2nd letter automatically goto next cell. How we will set this. Help me.
  • To post as a guest, your comment is unpublished.
    Eric · 5 years ago
    Hello. Is it possible to stop when it reaches a certain character?
    For example, I want it to stop with a warning message when it exceeds 80 characters before I hit "enter."

    Thank you.
  • To post as a guest, your comment is unpublished.
    Viraf · 5 years ago
    Scenario Column width which can take 55 characters maximum, I have set up Data validation in Settings Text Length, Between, Minimum 0 and Maximum 55 used Input message and Error Alert. When the data exceeds it brings up the message as mentioned in Error Alert of "Retry" to input again or "Cancel" to truncate excess characters. However, it is truncating the whole data entered and makes the line blank rather than truncating any excess characters, what I want to achieve here is if there are any excess characters entered in column E over 55 to be truncated and not the full data.
    How can I achieve truncation only excess characters and not the whole data entered, I s there a way I can achieve this and the data is entered by the customer so I do not want them to get confused as to what needs to be done.
    Thanking you in advance.
  • To post as a guest, your comment is unpublished.
    Ivan · 5 years ago
    Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?
  • To post as a guest, your comment is unpublished.
    Vijay Devjani · 5 years ago
    Hi Pal.

    Data validation doesnt work on pivot table. so what to do when i want to restrict the size of cells in a pivot table.
  • To post as a guest, your comment is unpublished.
    Jenn · 5 years ago
    I am finding a way to restrict user to entering too many Korean and English characters in excel file. As Korean characters are in double bytes and English characters are in single bytes, it seems impossible for me to use data validation. Is there any way I can try to combine both in data validation so that user doesn't enter more than 25 bytes?
  • To post as a guest, your comment is unpublished.
    Sokunth · 6 years ago
    I have two condition.
    1 - If Column B1 = A Set Text Length = 6
    2 - If Column B1 = B Set Text Length = 13

    Please Guide Me!
  • To post as a guest, your comment is unpublished.
    mark mandane · 6 years ago
    thanks so much for the limiting of unputs. It helps a lot!
  • To post as a guest, your comment is unpublished.
    sinks · 6 years ago
    Thank you, very helpful!
  • To post as a guest, your comment is unpublished.
    charlotte · 6 years ago
    i created a form with multiple rows that will be interactive and filled in by my staff. The problem is they are typing everything in one row creating an extremly long row, when there are still several unused rows below.

    How can I put a limit on the characters in each row? Is there a way in excel for the data to move automatically to the new row, after the first row exceeds its character limit? Please help.
  • To post as a guest, your comment is unpublished.
    sjlrl · 6 years ago
    #Manish You could use the LEFT(cell,30) function. Say your data is in A1. At A2 enter =LEFT(A1,30) then copy A2 to A1
  • To post as a guest, your comment is unpublished.
    jack · 7 years ago
    Is it possible do the following 2 functions. I need to limit a cell to 40 characters with existing characters.
    After concatenating two cells the characters being added to the right of the cell to end up to the far right.
  • To post as a guest, your comment is unpublished.
    Manish Singh · 7 years ago
    for a already filled sheet the above rule is not applicable.

    please provide guidance for already filled sheet.
  • To post as a guest, your comment is unpublished.
    Marcela · 7 years ago
    I'm truly enjoying the design and layout of your blog.
    It's a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out
    a designer to create your theme? Fantastic work!

    Also visit my weblog - [url=http://onlineedmeds03.com/]cialis online[/url]
  • To post as a guest, your comment is unpublished.
    DonnaC · 7 years ago
    I tried this but my entries have a combination of numbers and letters and many begin with a number. This will make all that begin with a number as invalid. How do I keep this from happening?
  • To post as a guest, your comment is unpublished.
    jemuel · 7 years ago
    thanks!! it helps me a lot!! :lol:
  • To post as a guest, your comment is unpublished.
    Tom · 7 years ago
    I have used the data validation to set a character limit. However, if the limit is exceeded and the warning message appears, you can just press cancel with no consequence. How do I prevent this?
  • To post as a guest, your comment is unpublished.
    dora · 7 years ago
    I want to make sure that the cell is not empty. User can type another value, but shouldn't be able to delete it. I used text length, given that it has to be min. 1 character. I have set input message and error alert as well. Doesn't work. Tried with ticking ignore bland and without it as well. No luck. You can still delete the value. (If I use "less than", that works fine with message and alert.)
  • To post as a guest, your comment is unpublished.
    Shashi · 7 years ago
    Really very Helpful....... thank you very much
  • To post as a guest, your comment is unpublished.
    Shashi · 7 years ago
    Really Helpful... thank you very much
  • To post as a guest, your comment is unpublished.
    Vara Vemula · 7 years ago
    while converting txt to excel if the txt file contain a cell value more than 15 characcter it is rounding off. is there any formulat that helps to rpevent this.
  • To post as a guest, your comment is unpublished.
    Subhadeep · 7 years ago
    Very helpful, easy to learn. This was great, nice step by step instructions. Thank you.
  • To post as a guest, your comment is unpublished.
    Smithd476 · 7 years ago
    This design is incredible! You obviously know how to keep a reader amused. dakgffedggeddgfe
  • To post as a guest, your comment is unpublished.
    Roy · 7 years ago
    I need to limit numerical inputs to show just the last 5 characters regardless of string size.
    can this be done in Excel?
  • To post as a guest, your comment is unpublished.
    JENN · 7 years ago
    use the following formula:
    =LEFT(cell #,# of characters you want to limit the field down to)
    Example:
    =LEFT(C1,30)
    • To post as a guest, your comment is unpublished.
      Dedi · 1 years ago
      thanks amazing
    • To post as a guest, your comment is unpublished.
      Ramesh · 1 years ago
      in the next cell i need the characters from 31 to 60. so wat is the formala for that??? plz guide me

    • To post as a guest, your comment is unpublished.
      Lynell · 5 years ago
      Thank you so much! This is exactly what I needed!!
  • To post as a guest, your comment is unpublished.
    Webstarr · 7 years ago
    Is it possible to set a data validation on a cell containing a concatenate formula? I am concatenating several cells' values and would like to warn the individual entering data if the count exceeds 50 characters. However, I don't want to use the =LEFT function as I need the user to edit his input values, rather than have Excel only return the first 50 characters.

    Any ideas?
  • To post as a guest, your comment is unpublished.
    RedHair4ever · 7 years ago
    Is it possible to use this with a scanner for barcode? After the scan is done (X digits) can "enter" be done automatically so it will positionned itself in the next cell so I can scan lots of items without to manually press enter ?

    Any help will be welcome, I don't have any idea how to resolve this.
  • To post as a guest, your comment is unpublished.
    bachocron · 7 years ago
    This was great, nice step by step instructions!
  • To post as a guest, your comment is unpublished.
    Misty · 7 years ago
    Thank you! Very helpful!
  • To post as a guest, your comment is unpublished.
    Mudassar · 7 years ago
    Really helpful. thanks a lot :-)
  • To post as a guest, your comment is unpublished.
    Bart · 7 years ago
    Thanks for the information.

    Is it possible to limit existing text in a column to 30 characters and erase everything that exceeds that limited amount of characters?

    Thank you
    • To post as a guest, your comment is unpublished.
      Matthiasagreen · 7 years ago
      Use text to column, choose fixed width and choose the character count you want. It will separate antything above the limit to a new column that you can delete.
  • To post as a guest, your comment is unpublished.
    Rutger · 7 years ago
    The data validations to limit text length input are clear, but unfortunately validations stop the moment you copy text from another field which exceeds the max in the target field.

    Can that be prevented in some way?

    Would appricate userful respons!
    • To post as a guest, your comment is unpublished.
      Matthiasagreen · 7 years ago
      Use text to column, choose fixed width and choose the character count you want. It will separate antything above the limit to a new column that you can delete.
      • To post as a guest, your comment is unpublished.
        Ashish · 7 years ago
        It's not feasible @ end user is using the sheet.
      • To post as a guest, your comment is unpublished.
        Friday5 · 7 years ago
        Can you provide some instructions? Not sure how to accomplish what you are saying. What does "text to column" mean?
  • To post as a guest, your comment is unpublished.
    NyPy · 7 years ago
    This is really helpful, is there anyway to make this count spaces too?