I have the following start cell A1
GEMMA 30 X 44 = ARIANE DARK BEIGE
GEMMA 30 X 44= ARIANE GREY DARK
GEMMA 30 X 44 = ARIANE IVORY
GEMMA 30 X 44 = ARIANE FLOWER GOLD PANEL DECOR
I want the formula to mark Y if the word = does has one space before and after
Please Login or Register to view this content.
Last edited by makinmomb; 05-02-2015 at 01:43 AM.
Perhaps this...
GEMMA 30 X 44 = ARIANE DARK BEIGE Y GEMMA 30 X 44= ARIANE GREY DARK GEMMA 30 X 44 = ARIANE IVORY Y GEMMA 30 X 44 = ARIANE FLOWER GOLD PANEL DECOR Y
B2=IF(ISERROR(SEARCH(" = ",A2)),"","Y")
copied down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I interpreted this differently. I went for "one space and one space ONLY". This will throw up errors for no spaces and more than one space.
=IF(AND(CODE(MID(A1,FIND("=",A1)-2,1))<>32,CODE(MID(A1,FIND("=",A1)-1,1))=32,CODE(MID(A1,FIND("=",A1)+1,1))=32,CODE(MID(A1,FIND("=",A1)+2,1))<>32),"Yes","No")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Good point, Glen, modified my suggestion to this...
=IF(OR(ISERROR(SEARCH(" = ",A2)),NOT(ISERROR(SEARCH(" ",A2)))),"","Y")
It also tests for more than 1 space
Ford... are you sure about your formula at Post #4?
Glen...
GEMMA 30 X 44 = ARIANE DARK BEIGE Y GEMMA 30 X 44= ARIANE GREY DARK GEMMA 30 X 44 = ARIANE IVORY Y GEMMA 30 X 44 = ARIANE FLOWER GOLD PANEL DECOR Y GEMMA 30 X 44 = ARIANE DARK BEIGE
B2=IF(OR(ISERROR(SEARCH(" = ",A2)),NOT(ISERROR(SEARCH(" ",A2)))),"","Y")
That last 1 doesnt show too well, but there are 2 spaces before the =
OK. What am I doing wrong???
Try this in C1 and copy down...
=IF(AND(NOT(ISERROR(SEARCH(" = ",A1))),ISERROR(SEARCH(" ",A1))),"Yes","")
Jhren. Just like Ford's formula. I get nothing in all cells. it evaluates to an error.
Hmm... worked fine on your file when I put it together.
Downloaded your file again, copy/pasted my formula, and nothing...???
Yup. That's what I did, too. Nothing...
Figured it out... I think. The forum is parsing out the double space when not enclosed in CODE tags. Try this...
Please Login or Register to view this content.
Woo Hoo!!!
One problem solved and another created. What happens if there's a double space between any other two words (e.g. Gemma and 30)? Your formula and Ford's don't like iit...
I thought about that... computing, computing...
Please Login or Register to view this content.
Try this:
=IF(LEN(TRIM(MID(A1,FIND(" = ",A1&" = ")-2,7)))=7,"Y","")
What's happened if there was char(160) instead of char (32)? all of yours would hate them?
Quang PT
If we're going to get picky about what kind of space (other than a 'space bar' space) we need to know which, if any, are acceptable.
In unicode there are 25 characters defined as whitespace characters:
http://en.wikipedia.org/wiki/Whitespace_character
Thanks FDIBBINS since the answer was opposite
I added this on C1 to find those that I am looking for
Please Login or Register to view this content.
FWIW, TRIM does not replace a CHAR(160) "no-break space" with a CHAR(32) "space". Not sure about other whitespace characters.
Otherwise
I used
instead ofPlease Login or Register to view this content.
Please Login or Register to view this content.
Or this
=IF(ISNUMBER(--MID(A1,FIND("=",A1)-1,1)),"Y","")
Row\Col GEMMA 30 X 44 = ARIANE DARK BEIGE GEMMA 30 X 44= ARIANE GREY DARK Y GEMMA 30 X 44 = ARIANE IVORY GEMMA 30 X 44 = ARIANE FLOWER GOLD PANEL DECOR
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks