+ Reply to Thread
Results 1 to 8 of 8

Using vba -how do I remove characters from right of space on all cells in specific columns

  1. #1
    Registered User
    Join Date
    06-08-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    4

    Using vba -how do I remove characters from right of space on all cells in specific columns

    Sorry if this is a stupid question, but I am new to vba and I am trying to remove characters from the right of a space

    an example of the text in the cell is - 5/08/2015 at 12:22 PM

    I want to reduce it to 5/08/2015 (formatted as d/mm/yyyy)

    Please Login or Register  to view this content.
    this removes the time from the end of the one cell, but I am struggling to get it to work on all cells within 3 columns ("E:G")

    Can anyone assist please?
    Last edited by gbegnell; 06-08-2021 at 11:28 PM. Reason: copy of file attached

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-08-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    4

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    thank you. appreciate your assistance

  4. #4
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    Hello GBegnall,

    Try it as follows:-

    Please Login or Register  to view this content.
    If your data starts in E3, then change E1 in the code to E3.

    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 06-08-2021 at 11:10 PM. Reason: New code

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    See if this works.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-08-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    4

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    Please Login or Register  to view this content.

    This works but not all cells format as dates

    When I use the column filter only some of the cell values list within the relevant year.

    Attachment 735840

    even though the entire column is formatted as date d/mm/yyyy

  7. #7
    Registered User
    Join Date
    06-08-2021
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    4

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    Please Login or Register  to view this content.

    This also works but not all cells format as dates

    When I use the column filter only some of the cell values list within the relevant year.

    Attachment 735840

    even though the entire column is formatted as date d/mm/yyyy
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by gbegnell; 06-08-2021 at 11:57 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Using vba -how do I remove characters from right of space on all cells in specific col

    Your attachments are all invalid, so can not open.

    my guess,
    Please Login or Register  to view this content.
    Last edited by jindon; 06-09-2021 at 05:50 AM.

+ 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. [SOLVED] How to remove first 8 characters (including space) in a string
    By bala04msw in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2021, 06:53 AM
  2. [SOLVED] Create a formula to remove last two characters from a cell if the 2nd character is a space
    By SellMeTv2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2019, 11:13 PM
  3. Remove all characters before space in line
    By gartie in forum Excel General
    Replies: 9
    Last Post: 08-29-2012, 05:52 PM
  4. [SOLVED] Trim Specific Values and Remove last right space
    By jantonio in forum Excel General
    Replies: 3
    Last Post: 05-02-2012, 08:40 AM
  5. Replies: 3
    Last Post: 04-05-2012, 04:58 PM
  6. Remove all characters after space
    By vsantoro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2010, 04:46 PM
  7. Remove varying amounts of space characters
    By Access Joe in forum Excel General
    Replies: 4
    Last Post: 01-13-2006, 06:30 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