+ Reply to Thread
Results 1 to 17 of 17

Replace any number of spaces with comma

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Replace any number of spaces with comma

    Hello everyone
    I have a string that has sometimes one space and some times two spaces and may be three or more ..
    I need to replace any number of spaces that are more than one space with a comma
    Example :

    "This is just an example of string"

    The output would be "This is, just an example, of String"

    I would prefer a UDF for that purpose
    Thanks advanced for help
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Replace any number of spaces with comma

    Try the TRIM function to remove extra spaces
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    Thanks a lot for reply
    I know how to use Trim but I don't need to trim .. I need to deal with the spaces more that two spaces
    Any more than two spaces to be replaced with comma

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    Maybe :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    That's wonderful Mr. Karedog
    It works like charm

    I am greedy as you know .. How to add another point
    Example :
    Please Login or Register  to view this content.
    The needed output would be (Remove any commas and dashes and the spaces more than two with comma (as done)
    The desired output would be
    Please Login or Register  to view this content.

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    Should be :
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    That's perfect
    The last point : What if there are comma or dash at the end of the string ..? I need to remove any commas or dashes at the end
    Thank you very much

  8. #8
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    Should be :
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    You are amazing Mr. Karedog
    That's exactly what I was searching for
    Thank you very very much for great help
    Best and kind regards my hero

  10. #10
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    You are welcome, thanks for marking the thread as solved and rep.points.

    Regards

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    As a matter of curiosity : Can UDF in post 4 be executed by formulas?

  12. #12
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    I don't get it.
    Do you mean : can the UDF called from cell ? If this is the question, the answer is yes.

  13. #13
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    No I don't mean that. I mean to replace any spaces more than one using formulas (Built-In ones) like Substitute for example

  14. #14
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    Maybe like this ?
    https://www.extendoffice.com/documen...gle-space.html

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    I mean to replace those spaces more than two with comma .. not with space
    The Trim function does that too ..

  16. #16
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Replace any number of spaces with comma

    I don't know, you better ask for it in "Excel Formulas and Functions" subforum.

    Regards

  17. #17
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Replace any number of spaces with comma

    Thank you very much Mr. Karedog for your great and awesome support

+ 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] Remove consecutive spaces and replace with single comma
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-11-2015, 05:20 PM
  2. [SOLVED] Code to Replace Comma with dot and dot with comma
    By BS Singh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2014, 06:10 PM
  3. [SOLVED] Remove spaces and replace with comma.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2013, 01:01 PM
  4. Replies: 8
    Last Post: 03-15-2012, 02:27 PM
  5. 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
  6. replace the comma separator to a dot and dot to a comma at the same time
    By barkiny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2010, 05:42 PM
  7. Removing spaces and adding comma's using a Macro
    By Jeroen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2006, 05:55 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