+ Reply to Thread
Results 1 to 14 of 14

Macro to remove spaces in a number

  1. 06-10-2021, 07:58 AM #1
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Macro to remove spaces in a number

    I have numbers in Col C & D that are imported

    The numbers have spaces in between for eg 459 221,225 985 etc the should look like 459,221 , 225,985 after running the macro

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-10-2021, 08:29 AM #2
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Macro to remove spaces in a number

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.
    Reply With QuoteRegister To Reply
  3. 06-10-2021, 09:39 AM #3
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Re: Macro to remove spaces in a number

    Hi Mumps

    Thanks for the help. The space in the numbers are not being removed. see Attached Sample as well as sheet "Correct Data" showing what the data should look like after running the macro
    Attached Files Attached Files
    Last edited by Howardc1001; 06-10-2021 at 09:55 AM.
    Reply With QuoteRegister To Reply
  4. 06-10-2021, 09:47 AM #4
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,958

    Re: Macro to remove spaces in a number

    should look like 459,221 , 225,985
    So are you wanting a comma between numbers?
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  5. 06-10-2021, 09:53 AM #5
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Macro to remove spaces in a number

    This is what I get after running the macro:

    Revenue 259,213 226,171
    Cost of sales (157,486) (810,673)
    Reply With QuoteRegister To Reply
  6. 06-10-2021, 10:01 AM #6
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,958

    Re: Macro to remove spaces in a number

    This works...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-10-2021, 10:02 AM #7
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Re: Macro to remove spaces in a number

    hi Mumps

    When I run the macro, The spaces are not removed and formatted with Thousand seperator

    Not sure why it is working on your PC
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-10-2021, 10:04 AM #8
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Macro to remove spaces in a number

    Did you try @sintek's solution?
    Reply With QuoteRegister To Reply
  9. 06-10-2021, 10:12 AM #9
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Re: Macro to remove spaces in a number

    Hi Sintek

    For some unknown reason, I cannot get this to work on my PC

    See attached sample data with your code
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 06-10-2021, 10:19 AM #10
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Macro to remove spaces in a number

    Try:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 06-10-2021, 10:20 AM #11
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,958

    Re: Macro to remove spaces in a number

    Works for me...see attached result...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 06-10-2021, 10:26 AM #12
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Re: Macro to remove spaces in a number

    Hi Sintek & Mumps

    I found the problem. I was to do with memory. I closed down Excel , reopened and then ran the macro and it worked

    Many thanks for all your help
    Reply With QuoteRegister To Reply
  13. 06-10-2021, 10:32 AM #13
    Sintek
    Sintek is offline
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,958

    Re: Macro to remove spaces in a number

    As always...a pleasure Howard...happy coding...thanks for rep +
    Reply With QuoteRegister To Reply
  14. 06-10-2021, 11:15 AM #14
    Mumps1
    Mumps1 is offline
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Macro to remove spaces in a number

    Glad it worked out.
    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. [SOLVED] Remove Trailing Spaces VBA Macro
    By Bob1955 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-08-2021, 07:43 PM
  2. [SOLVED] Extract a mobile number from string AND remove the spaces?
    By Akshay in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2018, 11:05 AM
  3. [SOLVED] Macro remove leading spaces
    By hftechno in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-30-2018, 09:47 AM
  4. [SOLVED] Need to remove the first 2 characters in string plus spaces on the left with macro
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-15-2014, 06:11 AM
  5. remove spaces and characters in hyperlink - macro
    By missy22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2013, 09:23 AM
  6. Using the Replace to remove Spaces in a Number
    By TWent in forum Excel General
    Replies: 5
    Last Post: 05-09-2011, 02:34 PM
  7. macro to remove spaces at the end of a cell
    By Michael A in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-12-2005, 08:06 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