Results 1 to 8 of 8

Thread: [RESOLVED] Formula to check the length of the value

  1. Mar 28th, 2009, 05:50 AM #1
    JJJCR_FOX
    JJJCR_FOX is offline

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Resolved [RESOLVED] Formula to check the length of the value

    hi guys, is there a formula in conditional formatting to check the length of the value in all the cells in one column? or it's only possible through a macro? thanks...
    The taller the bamboo grows the lower it bends...
    Reply With Quote Reply With Quote
  2. Mar 28th, 2009, 06:01 AM #2
    Lord Orwell
    Lord Orwell is offline
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,628

    Re: Formula to check the length of the value

    are you wanting to simply resize your column to the widest one? Also, is this excel?
    My light show youtube page (it's made the news) www.youtube.com/@lightsofelberfeld
    Contact me on the socials www.facebook.com/lordorwell
    Reply With Quote Reply With Quote
  3. Mar 28th, 2009, 06:45 AM #3
    anhn
    anhn is offline
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Formula to check the length of the value

    Why check the length in conditional formatting? What do you want to do with it?
    Usually you can use a formula to check the length as:
    =LEN(A1)
    That can also be used in conditional formatting.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk
    Reply With Quote Reply With Quote
  4. Mar 28th, 2009, 08:03 PM #4
    JJJCR_FOX
    JJJCR_FOX is offline

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: Formula to check the length of the value

    hi anhn, thanks for the reply... yup.. i already created a macro to do this task..

    but just want to know whether it can be simply done by a formula..

    like to check the duplicates just use the countif formula and excel will automatically find the duplicate and highlight also...

    so..i just want to know whether there is a formula like "=len(a1)" but this formula will check for only one cell, how to check all the cells in one column and highlight also if the length is less than the specified length....

    why i want to check length using conditional formatting?
    >>>because i got a hundreds of data in one column and the length should be 9 digits.. so to make sure that there are no typo errors i need to check the length of each cell.
    thanks in advance...
    The taller the bamboo grows the lower it bends...
    Reply With Quote Reply With Quote
  6. Mar 28th, 2009, 09:16 PM #5
    anhn
    anhn is offline
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Formula to check the length of the value

    Select the range such as [A2:A200] (with [A2] is active cell) that you want to set Conditional Formatting.
    In CF, select Cells Formula, type =LEN(A2) then choose your own format.
    The formula for all other cells from [A3] to [A200] will be auto created with =LEN(A3) ... =LEN(A200)

    Nothed that, if set formula as =LEN($A$2) then all other cells will be the same with =LEN($A$2).
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk
    Reply With Quote Reply With Quote
  7. Mar 31st, 2009, 04:43 AM #6
    JJJCR_FOX
    JJJCR_FOX is offline

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: Formula to check the length of the value

    thanks anhn for the reply..so how to use the if statement in the len formula..for example if the length is less than 9 digits it will put a mark x on column b.. or is it possible to highlight the cell ... thanks.......
    The taller the bamboo grows the lower it bends...
    Reply With Quote Reply With Quote
  8. Mar 31st, 2009, 05:33 AM #7
    anhn
    anhn is offline
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Formula to check the length of the value

    Set Cells Formula =(LEN(A2) < 9) then click Format, set Pattern with color of your choice.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk
    Reply With Quote Reply With Quote
  9. Mar 31st, 2009, 07:59 AM #8
    JJJCR_FOX
    JJJCR_FOX is offline

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    233

    Re: Formula to check the length of the value

    thank you anhn, you're simply the best
    The taller the bamboo grows the lower it bends...
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width