+ Reply to Thread
Results 1 to 3 of 3

Split list without cutting a word in half

  1. #1
    Registered User
    Join Date
    08-26-2019
    Location
    Chattanooga
    MS-Off Ver
    2013
    Posts
    23

    Split list without cutting a word in half

    I am working on a workbook. Below is the best I know how to put it.

    Here I have a list of userid's.

    theflyingswan
    lisarenick105
    lisarenick105
    lisarenick105
    Marcello_ponco
    fsarlat
    GuslandiAriel
    JarredsmomKelly
    IndiaFord10
    daopen
    Jahid89734684
    Jahid89734684
    MavisZaina
    shankarbhil865
    Jahid89734684
    shankarbhil865
    kathleencrockes
    JohnUETrust
    MotivationBook6
    dannyt810
    weightLossHub24
    weightLossHub24
    floriansteps
    MotivationBook6
    GlobalZasha
    HaydonKathryn
    VirAstuceRegim
    Kada_soulayman
    HisandHerTrove

    I need the above divided into 240 ct charset. BUT when it splits is I need it to break a few charset early so I do not have to go back and clean it all up. Would be nice to use the symbol for a breakpoint to stop excel from breaking in the middle of a suerid. Below may be a better llok at what I want.

    Take these users to break into 240 charset.

    yogaowny
    hambrains100
    dannywo74964555
    IMQFT
    dannywo74964555
    fatlossmusclex
    babyboypics
    diethealth_tips
    ruccrocka
    ActionComplete
    vishne0
    PattersonLela1
    dherbs
    NutriCargo
    Hobble_Dragon
    BoutiqueCooks
    platz12345
    surfkook006
    Sarthakhans2
    VandaAmit
    marshawright
    DevChau63740719
    ElainePowellUK
    shubham288025

    Now lets say the 240 charset will be here.

    marshaw
    right
    DevChau63740719

    I would like to know how to stop it from splitting in the middle of a user id and have it insert two breaks after it and then pick up with the next full user id. Like below.

    marshawright
    DevChau63740719

    Thanks

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Split list without cutting a word in half

    If the top list (theflyingswan to HisandHerTrove) were in A1:A29, and you want to combine multiple user names separated by linefeed characters (decimal character code 10), you could use formulas like

    C1: =A1
    C2: =IF(LEN(A2)+D1<240,C1&CHAR(10)&A2,A2)

    Fill C2 down into C3:C29.

    D1: =LEN(C1)
    E1: =IF(D1>D2,"x","")

    Select D1:E1 and fill down into D2:E29.

    The col C formulas build up strings of user IDs up to 240 characters. The col E formulas produce x in rows with as long as possible strings in col C; otherwise, col E appears blank. This approach produces

    C18:
    theflyingswan
    lisarenick105
    lisarenick105
    lisarenick105
    Marcello_ponco
    fsarlat
    GuslandiAriel
    JarredsmomKelly
    IndiaFord10
    daopen
    Jahid89734684
    Jahid89734684
    MavisZaina
    shankarbhil865
    Jahid89734684
    shankarbhil865
    kathleencrockes
    JohnUETrust

    C29:
    MotivationBook6
    dannyt810
    weightLossHub24
    weightLossHub24
    floriansteps
    MotivationBook6
    GlobalZasha
    HaydonKathryn
    VirAstuceRegim
    Kada_soulayman
    HisandHerTrove

    C18 has 238 characters, and C29 has 157 characters.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Split list without cutting a word in half

    Start here.

    With user ids in A2:A25 put this length counter in column B.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and this cumulative counter in column C. In C2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then to locate the break
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    1
    2
    yogaowny
    8
    8
    marshawright
    3
    hambrains100
    12
    20
    4
    dannywo74964555
    15
    35
    5
    IMQFT
    5
    40
    6
    dannywo74964555
    15
    55
    7
    fatlossmusclex
    14
    69
    8
    babyboypics
    11
    80
    9
    diethealth_tips
    15
    95
    10
    ruccrocka
    9
    104
    11
    ActionComplete
    14
    118
    12
    vishne0
    7
    125
    13
    PattersonLela1
    14
    139
    14
    dherbs
    6
    145
    15
    NutriCargo
    10
    155
    16
    Hobble_Dragon
    13
    168
    17
    BoutiqueCooks
    13
    181
    18
    platz12345
    10
    191
    19
    surfkook006
    11
    202
    20
    Sarthakhans2
    12
    214
    21
    VandaAmit
    9
    223
    22
    marshawright
    12
    235
    23
    DevChau63740719
    15
    250
    24
    ElainePowellUK
    14
    264
    25
    shubham288025
    13
    277
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 10-20-2014, 03:09 PM
  2. [SOLVED] Split a Cell Reference in Half
    By nathanB in forum Excel General
    Replies: 3
    Last Post: 01-08-2013, 08:05 AM
  3. Is there a way to split a cell in half?
    By Millenium in forum Excel General
    Replies: 2
    Last Post: 10-02-2011, 07:14 PM
  4. Excel macro - split table/column in half
    By ron431800 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-15-2011, 12:35 AM
  5. Split a column in half?
    By Jeroen1000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2009, 03:01 AM
  6. How can I trim a Cell without cutting a Word in Half?
    By Enigmafish14 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2008, 12:20 PM
  7. placing a half line within split columns
    By m3site in forum Excel General
    Replies: 3
    Last Post: 08-27-2008, 05:13 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1