PDA

View Full Version : [SOLVED] Line Break Validation



lacviet2005
01-23-2018, 09:39 AM
Hi All,

I'd greatly appreciate for the help. I'm trying to validate a cell for line breaks and double-spacing with the below formula, but i couldn't get it to work. :doh:=OR(NOT(ISNUMBER(SEARCH(" ",A4)),FIND(CHAR(13),A4,1))))

Thank you.

georgiboy
01-23-2018, 09:59 AM
How about this for count of line breaks:

=LEN(A4)-LEN(SUBSTITUTE(A4,CHAR(10),""))

it might only be: CHAR(13) that's the issue.

hope this helps

lacviet2005
01-23-2018, 12:14 PM
How about this for count of line breaks:

=LEN(A4)-LEN(SUBSTITUTE(A4,CHAR(10),""))

it might only be: CHAR(13) that's the issue.

hope this helps

Thank you, Georgiboy. But that is not what i am looking for, because we are not allowed to alter the sheet. We use the spreadsheet to import data into the system. I would prefer to do a cell validation so that it alerts people to fix line breaks or extra spacing between words when they copy/paste into the spreadsheet.

lacviet2005
01-23-2018, 02:35 PM
Figured it out!

Aussiebear
01-23-2018, 03:34 PM
Figured it out!

Dont you just love it when people wont share the solution.......

lacviet2005
01-23-2018, 07:45 PM
Dont you just love it when people wont share the solution.......

🤪 will share tomorrow when I’m back in the office...geez! 😁

lacviet2005
01-24-2018, 08:15 AM
It's been awhile....
Anyway, not sure if this is helpful, but here's the formula for my line break and double-space validation: =AND(NOT(ISNUMBER(SEARCH(CHAR(13),$A4))),NOT(ISNUMBER(SEARCH(" ",$A4))))

FYI, I used CHAR(13) because i'm on Mac OS. Cheers!