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

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

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"

Cheers, DJ

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