Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Split Text Over x Characters

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Split Text Over x Characters

    Hi All

    Problem:

    I have a spreadsheet that uses sql to gather data from our servers. From this data, I use concatenate function to put the data into one cell ready to copy/paste into our sms program. The program will only take 160 characters at a time..

    Result I need:

    For the cell to split into two/three cells if over 160 characters (and to break after the last complete word), and if appropriate, it needs to say 1of2 at the beginning of the 1st message for example, and 2of2 at the beginning of the second.

    Is this possible?? If yes, how..?

    Thanks in advance for any help/advice.

  • #2
    Re: Splitting A Cell For Sms Messaging

    Give us a few typical "sentences" (short ones, long ones, ...) and also how the result should look like.

    Wigi
    Last edited by Wigi; June 27th, 2007, 17:23.
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: Splitting A Cell For Sms Messaging

      Also, try to incorporate my custom function here (you've already seen that function yesterday... )
      Regards,

      Wigi

      Excel MVP 2011-2014

      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

      Comment


      • #4
        Re: Splitting A Cell For Sms Messaging

        Thansk Wigi


        Example 1
        Hi Joe, for tomorrow you have 8 jobs, your first job is at Example, Example House, Example Street, Example Town. Arrival time 9:20. You must contact David at reception on arrival. Thanks
        ((186 characters inc spaces))

        Result Desired

        1of2 Hi Joe, for tomorrow you have 8 jobs, your first job is at Example, Example House, Example Street, Example Town. Arrival time 9:20. You must contact David
        ((160 characters inc spaces))

        2of2 at reception on arrival. Thanks
        ((36 characters inc spaces))

        Example 2

        Hi Joe, for tomorrow you have 8 jobs, your first job is at Example, Example House, Example Street, Example Town. Arrival time 9:20. Thanks
        ((138 characters inc spaces))

        Result Desired

        1of1 Hi Joe, for tomorrow you have 8 jobs, your first job is at Example, Example House, Example Street, Example Town. Arrival time 9:20. Thanks
        ((143 characters inc spaces))

        Comment


        • #5
          Re: Splitting A Cell For Sms Messaging

          I did have a look yesterday, struggled a little to understand properly what was going on with it- as you can tell I am not a vba specialist whatsoever! I am learning though!

          I dared not to refer to your post, or even suggest an answer to my problem incase the people that police the forum banned me again!! ;-)

          Comment


          • #6
            Re: Splitting A Cell For Sms Messaging

            Hi Stevo

            Have a look at the attachment. To have it 100% complete, you'll need a few extra formulas, e.g. to calculate how many parts there are in the message. Such that the 2 in the

            1of2

            is not hardcoded.

            Wigi
            Attached Files
            Regards,

            Wigi

            Excel MVP 2011-2014

            For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

            -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

            Comment


            • #7
              Re: Splitting A Cell For Sms Messaging

              Originally posted by stevo2820
              I did have a look yesterday, struggled a little to understand properly what was going on with it- as you can tell I am not a vba specialist whatsoever! I am learning though!
              Go on with struggling and searching, you learn much more from that compared to being given canned solutions.

              Originally posted by stevo2820
              I dared not to refer to your post
              You should've linked to it, such that others can play around with it too. It increases your chances to get a good answer.

              Wigi
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment


              • #8
                Re: Splitting A Cell For Sms Messaging

                Assume these text strings are in Column A. In B1 Enter

                ="1of"&CHOOSE((LEN(A1)>160)+1,1,2) & " " &IF(LEN(A1)>160,LEFT(A1,FIND(" ",A1,140)),A1)

                In B2 Enter;

                =CHOOSE((LEN(A1)>160)+1,"","2of2" & " " &SUBSTITUTE(A1,MID(C8,6,256),""))

                Copy down.

                Comment


                • #9
                  Re: Splitting A Cell For Sms Messaging

                  Hi

                  Just for fun, I tried putting together a formula that would scale as the string size in A1 increased (eg from 200 characters to 1500 characters say). It isn't perfect and will eventually fail due to the methodology of working out what the maximum "Page1ofX" is, but it should work for strings of at least several thousand characters. It isn't terribly efficient either but then it's a single formula that you enter in B1 and copy down as far as required). It is a CSE formula by the way so needs enetering with Ctrl+Shift+Enter. Please also see attached.

                  =TEXT(ROWS($A$1:$A1),"0\o\f" & INT((LEN($A$1)+LEN($A$1)/160*5)/160)+1 &" ")& MID($A$1,(ROWS($A$1:$A1)-1)*160+1-SUM(ROWS($A$1:$A1)*LEN(TEXT(ROWS($A$1:$A1),"0\o\f" & INT((LEN($A$1)+LEN($A$1)/160*5)/160)+1 &" ")))+LEN(TEXT(ROWS($A$1:$A1),"0\o\f" & INT((LEN($A$1)+LEN($A$1)/160*5)/160)+1 &" ")),160-LEN(TEXT(ROWS($A$1:$A1),"0\o\f" & INT((LEN($A$1)+LEN($A$1)/160*5)/160)+1 &" ")))

                  Best regards

                  Richard

                  EDIT: The other limitation is that after is has completed parsing the string, it still produces rows with additional text of the type "6of4 " - so you need to watch out for these. Just copy the rows up to the "...of4" or whatever limit.
                  Attached Files

                  Comment


                  • #10
                    Re: Split Text Over x Characters

                    All

                    Many thanks for your help and advice.

                    Dave's formula seems to work the best and which I will use

                    Many thanks to you all for your time and input

                    Comment


                    • #11
                      Re: Split Text Over x Characters

                      Hi,

                      In B1,

                      ="1of"&CHOOSE((LEN(A1)>155)+1,1&" "&A1,2 & " "&REPLACE(LEFT(A1,155),LOOKUP(9.999999E+307,FIND(" ",LEFT(A1,155),ROW(INDEX(A:A,1):INDEX(A:A,155)))),255,""))

                      In C1,

                      =CHOOSE((LEN(A1)>155)+1,"","2of2 "&MID(A1,LEN(B1)-3,255))

                      HTH
                      Kris

                      ExcelFox

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X