Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Split Text Over x Characters

  1. #1
    Join Date
    26th June 2007
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,298

    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 at 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) --

  3. #3
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,298

    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) --

  4. #4
    Join Date
    26th June 2007
    Posts
    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))

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th June 2007
    Posts
    4

    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!! ;-)

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,298

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    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) --

  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,298

    Re: Splitting A Cell For Sms Messaging

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

    Quote 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) --

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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.

  9. #9
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    26th June 2007
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Split Text Into Whole Words Not Exceeding X Characters
    By mpoisson in forum Excel General
    Replies: 7
    Last Post: November 27th, 2010, 02:44
  2. Split Function With Line Break Characters
    By bugmenot in forum Excel General
    Replies: 2
    Last Post: April 11th, 2008, 07:34
  3. Split Arabic Text Characters
    By Team in forum Excel General
    Replies: 5
    Last Post: March 24th, 2008, 16:11
  4. Split Words By Uppercase Characters
    By jonny in forum Excel General
    Replies: 3
    Last Post: September 20th, 2007, 19:51
  5. Split text over 255 characters
    By zjames in forum Excel General
    Replies: 4
    Last Post: March 6th, 2005, 14:23

Bookmarks

Posting Permissions

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