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.
Printable View
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.
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.Quote:
Originally Posted by georgiboy
Figured it out!
Dont you just love it when people wont share the solution.......Quote:
Originally Posted by lacviet2005
🤪 will share tomorrow when I’m back in the office...geez! 😁Quote:
Originally Posted by Aussiebear
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!