+ Reply to Thread
Results 1 to 23 of 23

Check space before and after specific word

Hybrid View

  1. 05-02-2015, 01:39 AM #1
    makinmomb
    makinmomb is offline
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,718

    Check space before and after specific word

    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

    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
    Last edited by makinmomb; 05-02-2015 at 01:43 AM.
    Reply With QuoteRegister To Reply
  2. 05-02-2015, 01:51 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Check space before and after specific word

    Perhaps this...
    A
    B
    2
    		 GEMMA 30 X 44 = ARIANE DARK BEIGE Y
    3
    		 GEMMA 30 X 44= ARIANE GREY DARK
    4
    		 GEMMA 30 X 44 = ARIANE IVORY Y
    5
    		 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
    Reply With QuoteRegister To Reply
  3. 05-02-2015, 02:01 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    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
    Reply With QuoteRegister To Reply
  4. 05-02-2015, 02:08 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Check space before and after specific word

    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
    Reply With QuoteRegister To Reply
  5. 05-02-2015, 02:26 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    Ford... are you sure about your formula at Post #4?
    Reply With QuoteRegister To Reply
  6. 05-02-2015, 02:31 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Check space before and after specific word

    Glen...
    A
    B
    2
    		 GEMMA 30 X 44 = ARIANE DARK BEIGE Y
    3
    		 GEMMA 30 X 44= ARIANE GREY DARK
    4
    		 GEMMA 30 X 44 = ARIANE IVORY Y
    5
    		 GEMMA 30 X 44 = ARIANE FLOWER GOLD PANEL DECOR Y
    6
    		 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 =
    Reply With QuoteRegister To Reply
  7. 05-02-2015, 02:38 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    OK. What am I doing wrong???
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-02-2015, 03:49 AM #8
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word

    Try this in C1 and copy down...

    =IF(AND(NOT(ISERROR(SEARCH(" = ",A1))),ISERROR(SEARCH(" ",A1))),"Yes","")
    Reply With QuoteRegister To Reply
  9. 05-02-2015, 04:05 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    Jhren. Just like Ford's formula. I get nothing in all cells. it evaluates to an error.
    Reply With QuoteRegister To Reply
  10. 05-02-2015, 04:14 AM #10
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word

    Figured it out... I think. The forum is parsing out the double space when not enclosed in CODE tags. Try this...

    =IF(AND(NOT(ISERROR(SEARCH(" = ",A1))),ISERROR(SEARCH("  ",A1))),"Yes","")
    Reply With QuoteRegister To Reply
  11. 05-02-2015, 04:11 AM #11
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word

    Hmm... worked fine on your file when I put it together.

    Downloaded your file again, copy/pasted my formula, and nothing...???
    Reply With QuoteRegister To Reply
  12. 05-02-2015, 04:14 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    Yup. That's what I did, too. Nothing...
    Reply With QuoteRegister To Reply
  13. 05-02-2015, 04:15 AM #13
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    Woo Hoo!!!
    Reply With QuoteRegister To Reply
  14. 05-02-2015, 04:22 AM #14
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Check space before and after specific word

    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...
    Reply With QuoteRegister To Reply
  15. 05-02-2015, 04:38 AM #15
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word

    I thought about that... computing, computing...
    Reply With QuoteRegister To Reply
  16. 05-02-2015, 04:45 AM #16
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word 

    =IF(AND(NOT(ISERROR(SEARCH(" = ",A4))),ISERROR(SEARCH("  =",A4)),ISERROR(SEARCH("=  ",A4))),"Yes","")
    Reply With QuoteRegister To Reply
  17. 05-03-2015, 11:21 AM #17
    István Hirsch
    István Hirsch is offline
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Check space before and after specific word

    Try this:

    =IF(LEN(TRIM(MID(A1,FIND(" = ",A1&" = ")-2,7)))=7,"Y","")
    Reply With QuoteRegister To Reply
  18. 05-03-2015, 11:31 AM #18
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,655

    Re: Check space before and after specific word

    What's happened if there was char(160) instead of char (32)? all of yours would hate them?
    Quang PT
    Reply With QuoteRegister To Reply
  19. 05-03-2015, 12:11 PM #19
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word

    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
    Reply With QuoteRegister To Reply
  20. 05-03-2015, 12:27 PM #20
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Check space before and after specific word

    FWIW, TRIM does not replace a CHAR(160) "no-break space" with a CHAR(32) "space". Not sure about other whitespace characters.
    Reply With QuoteRegister To Reply
  21. 05-03-2015, 12:23 PM #21
    makinmomb
    makinmomb is offline
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,718

    Re: Check space before and after specific word

    Thanks FDIBBINS since the answer was opposite

    I added this on C1 to find those that I am looking for

    =IF(B1="Y"," ","MARK")
    Reply With QuoteRegister To Reply
  22. 05-03-2015, 12:33 PM #22
    makinmomb
    makinmomb is offline
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,718

    Re: Check space before and after specific word

    Otherwise

    I used

    =IF(ISERROR(SEARCH(" = ",A2)),"Y","")
    instead of

    =IF(ISERROR(SEARCH(" = ",A1)),"","Y")
    Reply With QuoteRegister To Reply
  23. 05-03-2015, 12:43 PM #23
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Check space before and after specific word

    Or this

    =IF(ISNUMBER(--MID(A1,FIND("=",A1)-1,1)),"Y","")

    Row\Col
    A
    B
    1
    		 GEMMA 30 X 44 = ARIANE DARK BEIGE
    2
    		 GEMMA 30 X 44= ARIANE GREY DARK Y
    3
    		 GEMMA 30 X 44 = ARIANE IVORY
    4
    		 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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to get rid of a word and a space...
    By knuckledownlacrosse in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-08-2015, 06:26 PM
  2. Is there a word macro to help me (1) combine different word files and (2) add space?
    By studyboymark in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2015, 03:19 AM
  3. [SOLVED] How to check for a blank space and decide to add or not? Interesting
    By zjianguk in forum Excel General
    Replies: 8
    Last Post: 02-04-2013, 07:35 PM
  4. [SOLVED] VBA word (2003) codes to check if a table fits in a single page of the word document
    By samkumar in forum Word Programming / VBA / Macros
    Replies: 7
    Last Post: 08-27-2012, 09:35 AM
  5. Using the IF function, to make entering a word, space then word correct
    By NatNat in forum Excel General
    Replies: 13
    Last Post: 05-06-2009, 07:33 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1