Remove spaces between numbers in a cell

  • I am trying to add some more cleanup to a current Macro I have.

    What I need to do is to be able to go through and check cells in a particular column and remove and spaces and carriage returns that the user may have entered.
    The number of cells will vary each week, the column will always be the same, the amount of numbers in the cells could be 1, 2, 10 and so on (Normally wouldn't exceed 10)
    Below is an example of cell contents in an uncleaned condition.

    3073, 3075, 3077, 3078, 3080, 3085,
    3086, 3087, 3088, 3506, 3514

    This is what I need it to be:


  • removing spaces

    Removing spaces from a string is quite simple. you can do the following

    StringName = Replace(StringName, " ", "")

    As for removing carriage returns, I am unsure.

  • Hi jefsboys,

    If you wanted to do it with forumla you could use,

    =SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(10),"")

    Assuming your original cell is A1

    Or with code


  • This should take care of it...

    Notice that instead of removing characters, I'm creating a filter to only allow numbers and commas to flow thru. There's also a trick in there to overcome the problem with Excel wanting to interpret the string result as a number.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Andy,

    Works great!
    I changed the following because of a some other data I have in the sheet, it checks fine, do you see any problem with this? I changed where you had the offset at 0, 1 to 0, 0 so that it just overwrites the existing data.
    With rngTemp.Offset(0, 0) ' output in column B

    I tried it with no luck, I have a bunch of stuff going on in this code, probably something else I got there.

    Anyway, I have a solution for that part.

    One more thing.

    I am saving my results as a tab delimmeted file and Excel puts quotation marks around the cells. Is there anyway to have it so that excel does not put those quotes or to have the code remove them before saving?

    Here is a line of data from the results of that code.
    "3073,3075,3077,3078,3080,3085,3086,3087,3088,3506,3514" "1MMKT1,2MALMK,3MALMK,4MMKT7,5MMKT7,6MMKT1,7HHPLS,8MMKT1,9MMKT1,10MMKT1,11MMKT1,12M7CHI"

  • I offset the output so you could see the result. No problem over writing directly.

    Arron's code worked for me. You do have to select the data you want processed before running it though.

    The save as csv will put quotes around the data because it contains comma's
    To stop the quotes you can either write your own export routine.

    Or use the 'text to columns' process first before saving as csv


  • Excellent!

    I have it working beautifully.

    Aaron and Andy,

    Would Aaron's code be the preferred snippet of code to remove the carriage returns and do the cleanup due to being more thorough?

    This is a beautiful thing.

  • jefsboys,

    In case you need to do the clean-up without VBA, you could use the following formula:

    =SUBSTITUTE(SUBSTITUTE(CLEAN(A1),"""","")," ","")

    The outer SUBSTITUTE function gets rid of the Spaces, the inner one gets rid of the Quote Marks, and the CLEAN function removes the carriage returns.

    Alternatively, the above formula can be used in your code with the help of the Application.worksheetfunction command.



  • Quote from jefsboys

    Would Aaron's code be the preferred snippet of code to remove the carriage returns and do the cleanup due to being more thorough?

    Probably just a matter of personal preference.

    I prefer to define characters for inclusion. This way when the unforeseen odd character occurs my code will still do its job. Also, I like the "Select Case" setup because I can span multiple characters with the "To" feature. Also notice I don't need to spend time looking up the character codes for all those oddball characters I want to exclude.

    For instance, it's easy to change the filter from this...

    Case 0 To 9, ","

    To something like this to pickup the alphas and a few other special characters.

    Case "A" to "Z", "a" to "z", 0 To 9, ",", "!", "?"

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Use Replace function

    Here is a simpler way to do this,

    Just select the data, and say Ctrl+H ( For value replacement)

    In find what? give a space by clicking spacebar,

    In replace with- dont give anything and Click"Replace All",

    the spaces will be removed,

    hope it works,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!