+ Reply to Thread
Results 1 to 8 of 8

Pulling 2 pieces from the text variable and creating 2 variables separately?

  1. 02-13-2018, 11:24 AM #1
    Mirisage
    Mirisage is offline
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Pulling 2 pieces from the text variable and creating 2 variables separately?

    Hi Forum,
    I have an Excel dataset with one variable called "Text" with several hudred observations.

    Obs Text
    1 As on June 20, 2017 balance was $50,277.58 USD (share price is low $106.74)
    2 As on August 4, 2017 balance was $52,111 USD (share price is low $108.64)
    3 As on Sep 27, 2017 balance was $54,205.80 USD (share price is low $108.92)


    Q:
    I want to pull the date piece in the above "Text" variable and create a separate variable called "Var_1".
    Also I want to pull the USD values pieces and create a separate vaiable called "Var_2".

    Please see the attahced Excel file for a clearer view.

    So, far I have been extracting the two pieces and creating the two vairalbes manually which is strenous and error prone.

    Would appreciate if you could help me to automate the process.
    Thanks

    Mirisa
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-13-2018, 12:05 PM #2
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Pulling 2 pieces from the text variable and creating 2 variables separately?

    In D2:
    Please Login or Register  to view this content.
    This assumes the date always starts on the seventh character (eg, following "As on " string).

    In E2:
    Please Login or Register  to view this content.
    You may want to format column E as "Accounting" or something, since this will just display the value as a number.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
    Reply With QuoteRegister To Reply
  3. 02-13-2018, 01:01 PM #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: Pulling 2 pieces from the text variable and creating 2 variables separately?

    This returns the date, as a date that can be used in subsequent calculations. In addition, I think that there's a problem with ben's formula (the dollar formula didn't work for me...).
    Attached Files Attached Files
    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. 02-13-2018, 01:10 PM #4
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Pulling 2 pieces from the text variable and creating 2 variables separately?

    Quote Originally Posted by Glenn Kennedy View Post
    In addition, I think that there's a problem with ben's formula (the dollar formula didn't work for me...).
    ???

    Didn't work how? 'Cuz it worked perfectly fine on the three test ranges for me....
    Reply With QuoteRegister To Reply
  5. 02-13-2018, 01:17 PM #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: Pulling 2 pieces from the text variable and creating 2 variables separately?

    Could be a copy/paste thing.... I got #VALUE error.
    Reply With QuoteRegister To Reply
  6. 02-13-2018, 01:28 PM #6
    Zer0Cool
    Zer0Cool is offline
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Pulling 2 pieces from the text variable and creating 2 variables separately?

    Maybe I missed something, small data set but these should be simpler then whats been offered so far (I appended to @Glenn Kennedy's file) columns F , G and H:

    Gets Date:
    As Text
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    As Date (format cell as date)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Gets Dollar value (returns number, format cell as currency):
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    See attached

    EDIT: Just realized my solutions basically what @ben_hensel offered
    Attached Files Attached Files
    Last edited by Zer0Cool; 02-13-2018 at 01:31 PM.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me
    Reply With QuoteRegister To Reply
  7. 02-13-2018, 03:12 PM #7
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Pulling 2 pieces from the text variable and creating 2 variables separately?

    OR
    D2 
    Please Login or Register  to view this content.
    E2 
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  8. 02-14-2018, 03:02 PM #8
    Mirisage
    Mirisage is offline
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Re: Pulling 2 pieces from the text variable and creating 2 variables separately?

    Hello ben_hensel, Glenn Kennedy, Zer0Cool and nflsales,
    Many thanks to every one of you for the contribution.
    This is a great help and I got my problem soved.

    Thanks

    Mirisa
    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. How can I count multiple variables that are in 1 cell separately.
    By VashRazr83 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2015, 01:56 PM
  2. HALP! Creating a number of variables based on a variable.
    By MarmaladeLover in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2014, 10:06 AM
  3. USING LINEST FUNCTION to get 3 BETAS (3 x variables) for y variable (252 variables)
    By bobfoo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2012, 02:03 AM
  4. UserForms: Creating variable number of text boxes
    By seckela in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2012, 05:46 PM
  5. [SOLVED] Pulling Multiple Results by Matching one variable from List of Variables
    By caitlinkeats in forum Excel General
    Replies: 5
    Last Post: 04-09-2012, 07:24 PM
  6. Pulling multiple pieces of data to new sheet based on criteria
    By rwarbelow in forum Excel General
    Replies: 1
    Last Post: 08-08-2011, 06:00 PM
  7. Sorting numbers and text separately
    By Tim C in forum Excel General
    Replies: 8
    Last Post: 07-20-2005, 08:05 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