+ Reply to Thread
Results 1 to 10 of 10

How to remove text after the second instance of an asterisk?

  1. 06-18-2009, 01:52 PM #1
    JP Romano
    JP Romano is offline
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    How to remove text after the second instance of an asterisk?

    Hi All - having some trouble and hoping one of you experts can assist.

    I'm trying to convert variable length strings which are being copied from a display and loaded into an array.

    I have it working fine for the majority of the data, which comes in looking like "*ABC@US" or "*AABC@US"

    However, some of the data looks a bit different, particularly lacking the @ symbol. So what I end up with is
    "*ABC US*ABC US*ABC US"

    What I need to get to is just "ABC US" FYI the US part can be 1-5 characters.

    SO... I need a way to truncate anything after the second instance of the asterisk. Haven't been able to get it to work using various trim, mid, len, left, right, etc functions.

    Any ideas?

    Thanks!
    Last edited by JP Romano; 06-19-2009 at 03:38 PM. Reason: Solving
    Reply With QuoteRegister To Reply
  2. 06-18-2009, 01:57 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to remove text after the second instance of an asterisk?

    Something like this, perhaps:

    =MID(SUBSTITUTE(A1, "*", "_", 1), 2, FIND("*", SUBSTITUTE(A1, "*", "_", 1)) - 2)

    or even:

    =MID(A1, 2, FIND("*", SUBSTITUTE(A1, "*", "_", 1)) - 2)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 06-18-2009, 04:29 PM #3
    JP Romano
    JP Romano is offline
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: How to remove text after the second instance of an asterisk?

    Thanks - I tried it out, but am getting an error now that the Sub or Function is not defined. In my code I now have this:


    Please Login or Register  to view this content.
    Where
    Memory = variable containing the string I'm trying to grab (such as ABC*US)
    remSpace is a custom function that removes blanks in the string
    MyData(7) is a variable that conatains a much larger string, within which is the text I'm after. It's the 7th line on a screen copied into memory.

    In this case, Memory does = *, so the condition is met

    Do I need to have any special references for this to work? I may have neglected to mention that this lives in the VBA code, not on the spreadsheet cells.

    Thanks again, and I apologize if my explanation isn't too clear.
    Reply With QuoteRegister To Reply
  4. 06-18-2009, 04:41 PM #4
    JP Romano
    JP Romano is offline
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: How to remove text after the second instance of an asterisk?

    Changed my code to 
    Please Login or Register  to view this content.
    Which is no longer getting an error, but it's not stripping out the data after the second asterisk. Ugh.
    Reply With QuoteRegister To Reply
  5. 06-18-2009, 04:58 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to remove text after the second instance of an asterisk?

    Thanks again, and I apologize if my explanation isn't too clear.
    Still isn't.

    How about providing several example, and in each, underline the part you want returned.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 06-18-2009, 05:10 PM #6
    JP Romano
    JP Romano is offline
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: How to remove text after the second instance of an asterisk?

    Okay, sorry...
    I'm writing this in VBA via Excel 2007. I have a function which returns me a value like "*12345Z *12345Z"
    I want that to be "12345Z"

    Or it could return "*AAA1 *AAA1"
    I want that to be "AAA1"

    I've been staring at this too long, and it works for about 95% of the cases, which the user is satisfied with, but I'm not... I can probably figure out another way to do it if this isn't a no-brainer for you smart people!
    Reply With QuoteRegister To Reply
  7. 06-18-2009, 05:16 PM #7
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: How to remove text after the second instance of an asterisk?

    Do you perhaps mean ?

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    the latter would remove the * I believe if that's your wish...
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  8. 06-18-2009, 05:18 PM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to remove text after the second instance of an asterisk?

    I think you left out the "this is a programming inquiry", eh?

    Also, the formula I gave is a worksheet formula, and I am not sure how to actually search FOR AN ASTERISK in a string since all the string functions I know treat asterisks as wildcards....
    Reply With QuoteRegister To Reply
  9. 06-18-2009, 06:14 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to remove text after the second instance of an asterisk? 

    Please Login or Register  to view this content.
    and I am not sure how to actually search FOR AN ASTERISK in a string since all the string functions I know treat asterisks as wildcards.
    A tilde escapes the asterisk when it would otherwise be considered a wildcard character in worksheet function: ~*

    For VBA functions, it would get enclosed in brackets:[*]
    Last edited by shg; 06-18-2009 at 06:17 PM.
    Reply With QuoteRegister To Reply
  10. 06-19-2009, 03:38 PM #10
    JP Romano
    JP Romano is offline
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: How to remove text after the second instance of an asterisk?

    Thanks... I'm sure one of the suggestions will work. Much apprecaited, and apologies for the confusion!
    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)

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