• 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.

Need to limit characters in field

G

grades

New Member
Hi, I have a column of text fields varying in length. I need to separate them out into the next field based on # of characters.
I need no more than 30 characters in each field.
I need to split on the first space BEFORE the 30 char limit.
I don't really know how to do this going from the left, but here is what I have:
=IF(ISBLANK(K3),"",IF(LEN(K3)<25,SUBSTITUTE(K3,"|"," "),SUBSTITUTE(LEFT(K3,SEARCH(" ",K3,25)),"|"," ")))

This starts at 25th char and searches for spaces to the right, but if the last word is more than 5 characters then it goes over the 30 limit.
I could change it to 20 and hope that nothing is more than ten chars, but I need it to be reliable.
Also, I am substituting the | with a space.
Please help, thanks!
 
It's a pretty straight-forward issue.
Cell A1:
The quick brown fox jumped over the lazy dog

30 characters is this: The quick brown fox jumped ove

So I would like to see this:
The quick brown fox jumped

Ideally, it would be a formula to do the following:
1. find the 30th char
2. look left for the first space
3. output from the beginning to the space it found in step 2.

Thanks,
 
I can work out a formula for the rest of it on my own, based on how the first one is done.
But in the end it will need to go into the next cell.
 
100+ characters.
I will have to run the same formulas multiple times.

For the sake of example, let's say 45, such as the example above.
 
Hi,
For a string in A2, try
=LEFT($A2,LOOKUP(30,ROW(OFFSET(A$1,,,LEN($A2)))/(MID(A2,ROW(OFFSET(A$1,,,LEN($A2))),1)=" ")))

Cheers,
Sajan.
 
Hi, grades!

Just in case you want to handle the Nth chunk of certain length you could use this UDF:
Code: 
Option Explicit
 
Public Function sGetNthChunkOfMLengthSeparatedByXChar( _
        psText As String, _
        piChunk As Integer, _
        piLength As Integer, _
        psSeparator As String) _
            As String
    ' constants
    Const ksError = "Unsplittable text"
    ' declarations
    Dim I As Integer, J As Integer, A As String, B As String
    Dim iLastPosition As Integer, iChunkCount As Integer
    ' start
    A = psText
    If Len(A) < piLength Then A = A & String(piLength - Len(A), psSeparator)
    ' process
    iLastPosition = 0
    For I = 1 To Len(A)
        B = Mid(A, iLastPosition + 1, piLength)
        J = InStr(StrReverse(B), psSeparator)
        If J = 0 Then
            If I = 1 Then
                B = ksError
            Else
                B = ""
            End If
            Exit For
        Else
            B = Left(B, Len(B) - J + 1)
            iLastPosition = iLastPosition + Len(B)
            iChunkCount = iChunkCount + 1
        End If
        If iChunkCount = piChunk Then Exit For
    Next I
    ' end
    sGetNthChunkOfMLengthSeparatedByXChar = B
End Function

Give a look at the uploaded file. Yellow shading is just to indicate up to which columns is the formula copied.

Just advise if any issue.

Regards!
 

Attachments

  • Need to limit characters in field (for grades at chandoo.org).xlsm
    19 KB · Views: 0
I'm afraid i have no idea what any of that means.
Sajan's solution is clean, simple and works very well, thanks tho.
 
Hi, grades!

UDF are user defined functions written in VBA (Visual Basic for Applications) that is the programming language built-in Microsoft Office suite.

They're slower than native functions like SUM or VLOOKUP but they are used in the same way into formulas. The posted UDF is used in cell C1 as:
=sGetNthChunkOfMLengthSeparatedByXChar($A1;COLUMNA()-2;$B1;" ") -----> in english: =sGetNthChunkOfMLengthSeparatedByXChar($A1,COLUMN()-2,$B1," ")
where:
$A1 is the text to be parsed
COLUMN()-2 is the Nth chunk (1 for C, 2 for D, ...)
$B1 is the length of the chunk (30 in your case)
" " is the separator

An old link but useful to start with, if you're curious:
http://blogs.msdn.com/b/frice/archive/2004/06/11/153891.aspx

Regards!
 
You must log in or register to reply here.
Back
Top