+ Reply to Thread
Results 1 to 9 of 9

Extract Left of String

  1. 05-20-2018, 10:34 PM #1
    nicoan
    nicoan is offline
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Extract Left of String

    Hello all


    We have a column (A) like this:

    BTCUSDT
    BCCUSDT
    ETHUSDT
    LTCUSDT
    BCCBNB
    NEOUSDT
    QTUMUSDT
    BNBUSDT
    LTCBNB
    NEOBNB
    REPBNB
    XZCBNB
    BCCETH
    QTUMBNB
    WTCBNB
    NEBLBNB
    LSKBNB
    MCOBNB
    XRPUSDT
    ONTBNB
    DASHETH
    WANBNB
    ZECETH
    WAVESBNB
    NANOBNB
    PIVXBNB
    VENBNB
    NULSBNB
    AEBNB
    ICXBNB
    XMRETH
    DGDETH
    ADAUSDT
    STEEMBNB
    AIONBNB
    LTCETH
    RLCBNB
    VIABNB
    IOTABNB
    BCCBTC
    RDNBNB
    NAVBNB
    TRIGBNB
    NEOETH
    ETHBTC
    etc


    These are currency symbols in ratio format.
    Both the symbol and the base currency can have different character length.

    We want to extract the symbols only, separated from the base.
    The current possible bases (last letters) are:

    BNB
    USDT
    ETH
    BTC

    so as an example:
    BTCUSDT => BTC
    WAVESBNB => WAVES
    ETHBTC => ETH


    How do we do it?
    Reply With QuoteRegister To Reply
  2. 05-20-2018, 10:39 PM #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
    52,939

    Re: Extract Left of String

    It appears that the string length you want to extract varies in length for SOME entries?
    Are there many of these?
    How would you ID them?
    Are the > 3 only 4 characters, or are some longer?
    Can you give a more representative sample of what you want extracted?

    To get just the 1st 3 characters...
    =left(A1,3)
    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-20-2018, 10:54 PM #3
    nicoan
    nicoan is offline
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Extract Left of String

    Hi FDibbins and thank you very much for stepping in.

    Since both the symbols and the bases have different lengths we cannot use a fixed length.
    The bases are the mentioned 4
    BNB
    USDT
    ETH
    BTC

    And we need to extract everything at the left of those strings.
    I was thinking maybe with an OR function?
    Reply With QuoteRegister To Reply
  4. 05-20-2018, 11:03 PM #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
    52,939

    Re: Extract Left of String

    Quote Originally Posted by nicoan View Post
    ...
    The bases are the mentioned 4
    BNB
    USDT
    ETH
    BTC
    yet you gave another example in your 1st post...waves??
    Reply With QuoteRegister To Reply
  5. 05-20-2018, 11:04 PM #5
    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
    52,939

    Re: Extract Left of String

    Oh wait, I think I see what you mean now - those characters are at the END of the string, not the beginning

    Is USDT the only 4-character string there?
    Reply With QuoteRegister To Reply
  6. 05-20-2018, 11:08 PM #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
    52,939

    Re: Extract Left of String

    OK try this, assuming your data is in A1...
    =LEFT(A1,LEN(A1)-IF(RIGHT(A1,4)="USDT",4,3))
    copied down
    Reply With QuoteRegister To Reply
  7. 05-20-2018, 11:08 PM #7
    nicoan
    nicoan is offline
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Extract Left of String

    yep. Have you discovered an easier way?

    ie:
    WAVESBNB => WAVES
    SYMBOLBASE
    Getting rid off the BASE to get the SYMBOL.
    Reply With QuoteRegister To Reply
  8. 05-20-2018, 11:10 PM #8
    nicoan
    nicoan is offline
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Extract Left of String

    Ah, perfect..

    lol, I cannot send you rep cause you're answering all my questions in minutes..

    Thank you very much!! Appreciated.
    Reply With QuoteRegister To Reply
  9. 05-20-2018, 11:12 PM #9
    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
    52,939

    Re: Extract Left of String

    haha thats OK, Im happy I was able to help you
    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. [SOLVED] Extract cells (based on text contained) into left column (left and up) in front of data
    By Petrachabrova in forum Excel General
    Replies: 6
    Last Post: 06-26-2017, 09:04 PM
  2. [SOLVED] extract multi-letter string bits from string depending on size and case
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-18-2017, 10:41 PM
  3. [SOLVED] Extract numbers from a the left and to the right of a string
    By T86157 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2017, 10:38 AM
  4. [SOLVED] Extract all the words from a string for processing, while leaving string intact.
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-10-2014, 02:02 PM
  5. [SOLVED] Extract left characters from string with exception of 2 right characters
    By sweetkel23 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-16-2012, 10:45 PM
  6. [SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string
    By Buzzed Aldrin in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-04-2012, 01:49 PM
  7. Extract specifically formatted string from a larger string
    By thb in forum Excel General
    Replies: 16
    Last Post: 10-11-2011, 12:31 PM

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