+ Reply to Thread
Results 1 to 4 of 4

Split string with no delimiters to grab last word as numerical value in new column

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    United States
    MS-Off Ver
    2007
    Posts
    2

    Split string with no delimiters to grab last word as numerical value in new column

    I need a formula to extract the charge amount from text

    Chase bank only lets me download 90 days of transactions. To go further back, I am copy/pasting from older PDF statements and this is how it turns out:

    01/13 CHEWY.COM 800-6724399 FL 115.56
    01/14 AT&T*BILL PAYMENT 800-331-0500 TX 226.06
    01/15 AMAZON MKTPLACE PMTS AMZN.COM/BILL WA 21.89
    01/15 T.J. MAXX # 1228 CRANSTON RI 53.29
    01/15 TJMAXX #0315 WARWICK RI 239.50
    01/15 WHOLEFDS CRN 10254 CRANSTON RI 279.29
    01/16 NEW YORK TIMES DIGITAL 800-698-4637 NY 7.50

    I want to split this to three columns. One each for date, description, and amount.

    I am obviously able to use Text to Columns using fixed width to split the date from the rest. That creates two columns. Then I need to grab the dollar amount and split that to a 3rd column. I recorded a macro using my keyboard keys to edit, move the cursor backward to the final space character (^left-arrow 3x), then select to the end, cut the text, and paste. The VB created didn't capture my intent even closely, so I am back to using a formula.

    How can I create a 3rd column having only the transaction amount?

    Thanks Jamie

  2. #2
    Registered User
    Join Date
    06-11-2013
    Location
    United States
    MS-Off Ver
    2007
    Posts
    2

    Re: Split string with no delimiters to grab last word as numerical value in new column

    Never mind. I ate some dinner, my IQ went up, and then did a much better Google Search on "select last word in excel" and it gave me this

    =TRIM(RIGHT(SUBSTITUTE(text," ",REPT(" ",100)),100))

    and it works great.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,069

    Re: Split string with no delimiters to grab last word as numerical value in new column

    another way:

    =-LOOKUP(1,-RIGHT(A1,{1,2,3,4,5,6,7,8,9,10}))

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,453

    Re: Split string with no delimiters to grab last word as numerical value in new column

    Hi,

    The values and amount can be split out with the following in B1, C1 and D1. original string in A1

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Split the string based on delimiters
    By Reddiiiiii in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2017, 07:47 AM
  2. [SOLVED] How to split/grab number from string?
    By QuantEdge in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-18-2017, 04:57 PM
  3. [SOLVED] Search for a string, grab the numbers from adjoining column
    By noobis in forum Excel - New Users/Basics
    Replies: 15
    Last Post: 03-28-2016, 04:21 PM
  4. [SOLVED] Grab last word from string and then delete word from original string.
    By mtilbury in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-14-2015, 04:14 AM
  5. [SOLVED] How to Split a string and put each word in text box
    By MeSH16 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-12-2015, 12:38 AM
  6. Replies: 2
    Last Post: 04-09-2014, 11:48 AM
  7. Split text into array using multiple delimiters
    By TKFRMjarvis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-26-2014, 03:07 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1