Results 1 to 4 of 4

Thread: *Pls Help* need a formula to force a line break in each cell at the 24th digit/space

  1. #1

    Exclamation *Pls Help* need a formula to force a line break in each cell at the 24th digit/space



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All, I have extracted a stock report from MYOB (into Excel) so I can manipulate it and upload the stock info to our new POS system. My issue is that our new barcode printer has an item description limit of 24 characters per line. (The barcode printer prints our retail price tickets and the ticket format has a limit of characters 24, including spaces), therefore I need to know where the text will wrap/break so I can rewrite each line so that our tickets display enough readable information for our customers.

    eg. This is currently how one of the stock item cells reads, it's a designer kitchen tap with a pull out hose: "Slim D30 Kitchen Mixer Side Lever with Pull Out Spray".

    I need to find out if Excel has a formula that I can use to force a line break at the 24th character (in every cell in 1 column), so it ends up looking like this:

    "Slim D30 Kitchen Mixer S
    ide Lever with Pull out
    Spray"

    Or like this:

    "Slim D30 Kitchen Mixer
    Side Lever with Pull Out
    Spray"



    Thank you in advance! I urgently need your advice. My deadline is 13 June 2015. :/

    Cheers, DJ

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    If your original string is in A1, then in B1 try:

    =MID($A$1,(ROWS(B$1:B1)-1)*24+1,24)

    copied down until whole string is parsed.


  3. #3
    SEN.SATION.AL!!!

    That awesome formula worked like a charm! (I knew it was going to look something like that! lol)

    Super SUPER happy now! And so blessed you were around to answer it so quickly. It's 2.30am here and I was not expecting a response that fast, sending big hugs.

    THANK YOU!!

    I have 16,000 lines to clean up and now I can see exactly where the 25th character drops off! Yay!!!

    Have a great day!

    Cheers
    DJ

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    You are welcome.

    If you have 16,000 lines, then maybe it's better to copy across to parse, then copy down to get each row's info.

    e.g. If data is in column A, then in B


    =MID($A1,(COLUMNS($B1:B1)-1)*24+1,24)

    copied across say 5 columns or the max you think is needed to parse all data, then down your entire list.


Tags for this Thread

Posting Permissions

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