+ Reply to Thread
Results 1 to 7 of 7

How to Search on 3 Dash Sign?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-04-2015
    Location
    Jakarta
    MS-Off Ver
    15
    Posts
    154

    Thumbs up How to Search on 3 Dash Sign?

    How to find the word after the second dash or the third word?

    APPLE-PEANUT-EGG-BALL

    to get APPLE we can use LEFT search
    to get PEANUT we can use MID search
    to get BALL we can use RIGHT search
    ho to find the EGG?

  2. #2
    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: How to Search on 3 Dash Sign?

    If your data is in A1, use this...
    =TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",LEN($A1))),LEN($A1)*2+1,LEN($A1)))
    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

  3. #3
    Forum Contributor
    Join Date
    03-04-2015
    Location
    Jakarta
    MS-Off Ver
    15
    Posts
    154

    Re: How to Search on 3 Dash Sign?

    I'm sorry, I have problem to get BALL too. Please, help me...

  4. #4
    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: How to Search on 3 Dash Sign?

    I thought you already had that part?
    to get APPLE we can use LEFT search
    to get PEANUT we can use MID search
    to get BALL we can use RIGHT search
    ho to find the EGG?
    another way...
    =mid(a1,search("-",a1,3)+1,99)

  5. #5
    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: How to Search on 3 Dash Sign?

    If you want to break them all out into seperate columns...
    A
    B
    C
    D
    E
    1
    APPLE-PEANUT-EGG-BALL APPLE PEANUT EGG BALL

    B2=TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",LEN($A1))),LEN($A1)*(COLUMN()-2)+1,LEN($A1)))
    copied across

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to Search on 3 Dash Sign?

    Why not use Data - Text To Columns, Deliminated, with hyphen as the delimiter ?

  7. #7
    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: How to Search on 3 Dash Sign?

    Jonmo1, I just woke up, Im using that as my excuse for not thinking of that option

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Insert dash sign where not
    By makinmomb in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-18-2015, 06:07 AM
  2. [SOLVED] Help require to have a dash sign in the table
    By mso3 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-15-2015, 01:03 AM
  3. [SOLVED] Code correction in replacing dash sign while concatnet
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-18-2014, 11:44 AM
  4. [SOLVED] Ignore dash sign in formula subtotal
    By mukeshbaviskar in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-27-2014, 10:16 AM
  5. [SOLVED] Ignore dash sign while concatenate
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2013, 07:05 AM
  6. [SOLVED] code to insert dash sign
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-06-2013, 11:01 PM
  7. I see a dash, Excel sees a minus sign.
    By vishorizons in forum Excel General
    Replies: 3
    Last Post: 11-09-2009, 11:02 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1