+ Reply to Thread
Results 1 to 4 of 4

Removing a space after a comma

  1. #1
    DebbieK9
    Guest

    Removing a space after a comma

    I need to create a formula that removes a space after a comma, that separates
    two names, in a cell?

  2. #2
    gls858
    Guest

    Re: Removing a space after a comma

    DebbieK9 wrote:
    > I need to create a formula that removes a space after a comma, that separates
    > two names, in a cell?

    Assuming that the names you refer to are in the format Smith, John and both
    are in the same cell. You might consider using the text to columns feature,
    Data > Text to Columns, and split the cell into three columns one
    containing Last Name, one for the comma, and one for the First Name, then
    just delete the column with the comma. Probably best to have separate
    fields for first and last names anyway.

    gls858

  3. #3
    Guest

    Re: Removing a space after a comma

    Hi

    You could do it with a Find/Replace to do it in situ. If you want the result
    putting in another cell, have a look at the SUBSTITUTE function.
    Hope this helps.

    --
    Andy.


    "DebbieK9" <[email protected]> wrote in message
    news:[email protected]...
    >I need to create a formula that removes a space after a comma, that
    >separates
    > two names, in a cell?




  4. #4
    David McRitchie
    Guest

    Re: Removing a space after a comma

    Hi Debbie,

    REPLACE Worksheet Formula
    SUBSTITUTE(text, old_text, new_text, instance_num)

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

    VBA for a range, several examples
    Activesheet.Columns("A").Replace(What, Replacement, LookAt, _
    SearchOrder, MatchCase, MatchByte) '--replace method

    rng.Replace what:= ", ", replacement:= ",", _
    lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
    Intersect(Selection, Selection.SpecialCells(xlConstants, _ xlTextValues)).replace(", ",",")
    More information or examples in
    http://www.mvps.org/dmcritchie/excel/strings.htm
    http://www.mvps.org/dmcritchie/excel/proper.htm
    in combination with your Excel HELP and your VBA HELP

    --
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "DebbieK9" <[email protected]> wrote in message news:[email protected]...
    > I need to create a formula that removes a space after a comma, that separates
    > two names, in a cell?




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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