+ Reply to Thread
Results 1 to 11 of 11

Parsing into three pieces

  1. 01-20-2011, 02:20 PM #1
    Viewson is offline
    Registered User
    Join Date
    02-08-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8

    Parsing into three pieces

    Hi,

    I am new to vba and am stuck on how I get the below split into three columns?

    A1=102341,80USD0,00

    A2= 102341.80 (will also change the comma to a decimal afterwards)
    A3=USD
    A4=0,00 (will be deleted afterwards)

    Thanks
    Reply With QuoteRegister To Reply
  2. 01-20-2011, 02:30 PM #2
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Parsing into three pieces

    Do you need VBA ?

    Please Login or Register  to view this content.
    above assumes

    a) no leading zeroes in first number

    b) first number always has 2 decimals (denoted by comma delimiter)

    c) first number is followed by 3 letter currency code
    all can be adapted as necessary.
    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
  3. 01-20-2011, 04:28 PM #3
    Viewson is offline
    Registered User
    Join Date
    02-08-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Parsing into three pieces

    Quote Originally Posted by DonkeyOte View Post
    Do you need VBA ?
    Yes, I do need VBA. I was able to get it to work in a normal excel sheet, but not in visual basic. There are actually multiple lines of data which require the same formatting and is currently in column K of my worksheet.

    Here is a sample of additional lines:
    102341,80USD0,00
    31686,37USD0,00
    42541,81USD0,00
    89334,48USD0,00
    92535,84USD0,00

    The script I was attempting to use was:

    Please Login or Register  to view this content.
    What I need is for the currency "USD" to go into column I and the number (ex.102,341.80) to remain in column K.
    Last edited by DonkeyOte; 01-20-2011 at 04:59 PM.
    Reply With QuoteRegister To Reply
  4. 01-20-2011, 04:59 PM #4
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Parsing into three pieces

    Per Forum Rules - please always use CODE tags when posting VBA.

    Numerous approaches - no doubt plenty more efficient than the below but I'm rushing off I'm afraid so not much thought has gone into the below I'm afraid

    Please Login or Register  to view this content.
    change Sheet reference as per your requirements
    Reply With QuoteRegister To Reply
  5. 01-21-2011, 12:48 PM #5
    Viewson is offline
    Registered User
    Join Date
    02-08-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Parsing into three pieces

    Hi,

    I was able to get the below to work perfectly, however just as you mentioned above one of the assumptions came to light today.

    43956,USD0,00

    This value has no decimals listed as they are .00. Is it possible to account for this in the parsing?

    Quote Originally Posted by DonkeyOte View Post
    Per Forum Rules - please always use CODE tags when posting VBA.

    Numerous approaches - no doubt plenty more efficient than the below but I'm rushing off I'm afraid so not much thought has gone into the below I'm afraid

    Please Login or Register  to view this content.
    change Sheet reference as per your requirements
    Reply With QuoteRegister To Reply
  6. 01-21-2011, 01:23 PM #6
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Parsing into three pieces

    Is the trailing value always 0,00 ? (and/or always has 2 decimals)
    Reply With QuoteRegister To Reply
  7. 01-21-2011, 01:58 PM #7
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Parsing into three pieces

    Try:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 01-21-2011, 04:30 PM #8
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Parsing into three pieces

    Hello Viewson,

    Here is another method to accomplish the task using Regular Expressions. 
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  9. 01-21-2011, 04:43 PM #9
    Viewson is offline
    Registered User
    Join Date
    02-08-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Parsing into three pieces

    Yes, the trailing value is alway 0,00.

    Thanks, the new code did work.
    Reply With QuoteRegister To Reply
  10. 01-25-2011, 12:27 PM #10
    Viewson is offline
    Registered User
    Join Date
    02-08-2009
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Parsing into three pieces

    Hi Donkey,

    Ran into a small issue today, when using the code below for only one line item on my spreadsheet I get a runtime error 13, type mismatch. Is there a way to use it when there is only one line item and then multiple lines.

    Today's online line:
    165751,39USD0,00

    Pervious Multiple lines:
    102341,80USD0,00
    31686,37USD0,00
    42541,81USD0,00
    89334,48USD0,00
    92535,84USD0,00
    583075,02USD0,00
    91665,89USD0,00
    109809,74USD0,00
    459433,87USD0,00
    83198,76USD0,00
    386154,16GBP0,00


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 01-26-2011, 06:18 AM #11
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Parsing into three pieces

    Aplogies for belated response - I have not been online.

    You could try modifying vStr line to:

    Please Login or Register  to view this content.
    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