Create function to color if line over # of characters

Multiple lines can appear in a single cell.  I need a function since it is a shared Google doc and not a traditional Excel sheet.  The function should highlight any cells that contain a line over 20 characters.  

http://screencast.com/t/mChMFHOeh

Assistance is greatly appreciated.
frugalmuleAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rspahitzConnect With a Mentor Commented:
So it appears you are looking for a GoogleDocs solution (but since you created the question in the Excel section, I thought you were looking for Excel.)
So I'm not really familiar with Google's spreadsheets, but based on your link I see this:

Text      SPLIT      SPLIT(string, delimiter, treat_delimiters_individually)

  This will be a bit of work, but you should be able to use this to check to see if any of the resulting items has a length > 20.  Alternately, you can use the FIND function to repeatedly search for the "newline" delimiter (which might be Code(13) or Code(10) or a combination of Code(13)&Code(10), depending on the source of the data.

I hope that using this info and the script I shared above, you can make it work.
0
 
rspahitzConnect With a Mentor Commented:
You can do thris through conditional formatting.
Select the column which has the text, such as A and go to conditional formatting: menu Home | Styles Conditional Formatting
Then select New Rule and pick Use a formula to determine which cells to format
In the text box enter: =LEN(A1)>20
Below that, pick a format, such as setting the text red and click OK until you're back on the spreadsheet.
Any cell with more than 20 characters will highlight as you specified.
0
 
frugalmuleAuthor Commented:
The problem is that any 'cell' with more than 20 characters does not meet the requirement where any 'line' with more than 20 characters would meet the requirement.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rspahitzConnect With a Mentor Commented:
Let's see what I can come up with...

Try changing the conditional format condition to this:

=LongestLine(A1)>20

Open in new window


Then add this in a new VB module (Alt+F8, menu Insert | Module):
Function LongestLine(Cell As Range) As Integer
    Dim iLongestLine As Integer
    Dim strLines() As String
    Dim iLineCntr As Integer
    
    strLines = Split(Cell, vbLf)
    iLongestLine = 0
    For iLineCntr = 0 To UBound(strLines)
        If Len(strLines(iLineCntr)) > iLongestLine Then
            iLongestLine = Len(strLines(iLineCntr))
        End If
    Next
    
    LongestLine = iLongestLine
End Function

Open in new window

0
 
frugalmuleAuthor Commented:
The purpose of Google Docs is for sharing and the sharing component is far greater than what Office 360 can provide.

However, it limits us in some other ways.  This is the extent of what a google spreadsheet can do without a java script I think https://support.google.com/drive/table/25273?hl=en
0
 
frugalmuleAuthor Commented:
Here is a depiction of the scripting capabilities because we are limited in the traditional sense http://screencast.com/t/sbeprYyJyU3y
0
 
frugalmuleAuthor Commented:
I may have an alternate solution.  I think I can just reduce the columns and be able to tell which ones need changing that way.  Thanks for the tips.
0
 
rspahitzCommented:
You're welcome...I guess I need to start learning google sheets.
BTW...to use Code 13, you may need to use CHAR(13) in the split
0
 
frugalmuleAuthor Commented:
Yeah, I really like them.

It would be nice to use macros but the ability to share comments on each cell and even restrict editing based on a range is pretty big plus for us.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.