+ Reply to Thread
Results 1 to 8 of 8

Parse text in cell in last row

  1. #1
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Parse text in cell in last row

    I'm trying to change the text contents of the col A cell of the last row of the active worksheet. I can get the row with this code although I suspect there's a simpler way? But how do I then proceed to work on the contents of it please?

    Please Login or Register  to view this content.
    Terry, East Grinstead, UK
    Excel 365, Win 10 Pro, i7 PC, 4.0 GHz

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Parse text in cell in last row

    Hi Terry,

    Can you explain over again what you are tying to do? Your code is looking for the last row in column C, but then you want to do something in column A? Please elaborate.
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Parse text in cell in last row

    Thanks Jeff. My typo sorry. That 'C' should have been 'A'.

    But this morning I've done some revision of basics and solved the easy part of my question with this code. (Which also corrects another careless mistake: the previous version was selecting the first empty cell.)

    Please Login or Register  to view this content.
    As a non-programmer I find VBA's syntax hard going and I'd initially tried this which obviously failed:
    Please Login or Register  to view this content.
    However the harder part of my task is to change/convert the string content of that cell, 'myval', into Excel date format? I was originally thinking of keeping it as a string and manipulating/parsing it to change the display from 'Sunday 2 February' to 'Sun 2/2/20'. However I think a genuine date format would give me more flexibility?

    As a novice one of the topics I am having trouble getting my head around is date formatting. For example, if I open a blank worksheet and type '2/2/20' into it, which to me at this stage is a text string because the sheet format by default is set to 'General', then Excel cleverly changes it to '02/02/20', with a date format, which is almost always going to be what I intend. But if I type (or enter with VBA) 'Sunday 2/2/2020' or 'Sun 2/2/20', etc, then it remains a text string. Yet those formats are all available in the Custom drop-down list.

    Any help in making the conversion would be gratefully received please. FWIW I've attached a trivial worksheet.

    P.S. I see my sub's name is now misleading, but I'll leave that for now!
    Attached Files Attached Files
    Last edited by terrypin; 02-02-2020 at 08:33 AM.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Parse text in cell in last row

    Hi Terry,

    I'm still not quite sure what you are asking. A date in Excel is a number with it's origin counting from January 1, 1900 to now. If you enter 2/2/20 into Excel, Excel actually interprets that date as a number. 43863. Its been that many days since 1 Jan 1900.

    As for the custom format, that is just a mask of the number 43863. You can custom format that day anyway you want, but the underlying number is still the number.

    For example, if I open a blank worksheet and type '2/2/20' into it, which to me at this stage is a text string
    I suspect you are typing in 2/2/20 without the apostrophes and that is a number. Excel sees it as 43863 and not a text string.

    I don't understand why you would not just enter the date and then let Excel apply a custom format to it. It doesn't resonate with me to enter, Sunday 2/2/2020, and then have Excel convert it.

    Sorry, but I'm still having trouble seeing your ultimate goal. See if this example helps at all. I've put both a Worksheet_Change event and a Worksheet_BeforeDoubleClick event as options. It's set for column A, specifically, A1:A10.

    Maybe this would fit more for what you're seeking.
    Attached Files Attached Files

  5. #5
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Parse text in cell in last row

    Hi Jeff,

    Thanks, but I'm not sure what those two 'event subs' are meant to show me? They simply paste a constant date into any cell that's double-clicked. A welcome basic learning exercise as I've not used event-based subs like that before, so that's a good introduction. But I do know how to enter and format cells.

    I tried to avoid extraneous detail that I thought irrelevant and potentially confusing, but maybe I've done the opposite!

    Keeping it as succinct as possible, the reason my data is in text form is that it was generated externally by a script macro I developed in Macro Express Pro, which analyses large GPX files representing walk/hikes I've done over the last 25 years. Some of its fully-automated output are maps and elevation profiles, but the element under discussion that prompted my post is one of 25 text variables. Stuff like miles, duration, altitude etc. That text data is then made accessible to Excel by my MX Pro macro via the Windows clipboard. While it means leaving my MX Pro comfort zone, an important step is to get all 25 strings onto the first empty row of a workbook, WalkIndex.xlsm, which is very much faster to achieve in VBA.

    That element 'Sunday 2 February 2020' is used in another part of WalkIndex, as the title of a 'Finished Walk map'. But I could return to my MX Pro macro and change the text string output to 'Sun 2/2/20', and retain that as an extra text element in Excel. But my curiosity is piqued now, so I'd still like to learn how to do it in VBA
    Last edited by terrypin; 02-02-2020 at 01:16 PM.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Parse text in cell in last row

    But my curiosity is piqued now, so I'd still like to learn how to do it in VBA
    We must just be missing each other in understanding what you are trying to accomplish. I don't know what "it" is!

    I'll post a message for some extra eyes.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Parse text in cell in last row

    Please Login or Register  to view this content.
    Ben Van Johnson

  8. #8
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Parse text in cell in last row

    Excellent, thanks Ben, that works a treat!

    I spotted that slip in calculating the target row and corrected it. But that tricky splitting and reconstruction had me beat.

+ 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. Need to parse cell and count instances of specific text
    By robbyvegas in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-20-2015, 11:39 PM
  2. [SOLVED] Remove Blanc Space, parse text, convert text to measurement
    By plans in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-25-2015, 07:50 PM
  3. Macro to parse addresses and change text in a cell or cell range
    By mosey16 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2013, 10:15 AM
  4. [SOLVED] text formula to parse a cell, clean it, and dump into a fixed CELL RANGE - NO MACROS
    By James C in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-04-2013, 08:42 PM
  5. Macro to Parse Text - Import text to Excel from Multiple Text Files & Folders
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2012, 01:05 AM
  6. Parse text in cell, on fixed width
    By HMM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-10-2010, 04:25 PM
  7. Excel 2007 : Parse string of text from one cell
    By ssim in forum Excel General
    Replies: 3
    Last Post: 02-20-2009, 11:12 AM

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