+ Reply to Thread
Results 1 to 7 of 7

VBA, remove all characters within a string, except numerical characters and full stops

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    VBA, remove all characters within a string, except numerical characters and full stops

    I'm after a code that would remove all characters within a string that are not numbers (1234567890) and aren't full stops (.)


    So lets say I have a string of text "ts [$11.40 USD]" (without quotes) in col F
    Then after I would run the macro, it would only leave "11.40"
    I'm looking for an easy and efficient code.

    I have piece of code below that replaces "]" with blanks "". That is just an example that I would like to replace with something as described above.
    Any help is very much appreciated. Any ideas?


    Please Login or Register  to view this content.
    Last edited by rain4u; 02-25-2015 at 05:34 AM.

  2. #2
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    876

    Re: VBA, remove all characters within a string, except numerical characters and full stops

    Hey Rain,

    I have this Link stored..

    http://www.mrexcel.com/forum/excel-q...haracters.html
    1. Remove Numeric characters
    2. Remove Alphabetic characters
    3. Remove Non-Numeric characters
    4. Remove Non Alphabetic characters
    5. Remove Non Alpha-Numeric characters
    6. Remove Unprintable characters

    Check if this may help you in advance... as a Quick
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA, remove all characters within a string, except numerical characters and full stops

    Hey Parth
    Thank you for the link. I had a look of the link that you gave me, and played around with some codes but I could not achieve a code that it would only keep numbers and full stops. This one is pretty difficult to figure out....

    Though I'm sure somebody probably knows a genius way or has a out of the box thinking to achieve this. Any suggestions and ideas are very much welcome.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: VBA, remove all characters within a string, except numerical characters and full stops

    Maybe ...

    Please Login or Register  to view this content.
    Last edited by shg; 02-24-2015 at 03:01 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA, remove all characters within a string, except numerical characters and full stops

    Beatiful piece of code. It works like a charm. As I'm still learning and want to get better, can I leave some comments inside the code in terms of how I understand it. Am I more or less understanding it correctly?


    Please Login or Register  to view this content.
    Last edited by rain4u; 02-25-2015 at 05:36 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: VBA, remove all characters within a string, except numerical characters and full stops

    Here's another one to try..

    I have assumed (as you showed in your example).. that there is only one instance of the the xx.xx in each cell...

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: VBA, remove all characters within a string, except numerical characters and full stops

    Am I more or less understanding it correctly?
    Yup, you got it.

+ 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] VBA Remove First 10 Characters From string
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2013, 08:22 AM
  2. [SOLVED] Remove certain characters from the end of a string only
    By mini_dutch28 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-20-2012, 01:04 PM
  3. Remove characters in a string.
    By jncswe in forum Excel General
    Replies: 2
    Last Post: 09-10-2010, 07:26 AM
  4. Remove Characters From Right of String
    By milagros in forum Excel General
    Replies: 11
    Last Post: 12-26-2009, 12:01 PM
  5. Remove last 4 characters from cell string?
    By chris-uk-lad in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2009, 07:01 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