Share via

Is there an easy way to remove commas in an excel spreadsheet?

Anonymous
2011-05-09T20:29:22+00:00

I have imported addresses into an excel spreadsheet and unfortunately it has kept the commas and fullstops at the end of each line of address.  Is there a formula that I can use to remove these extra unwanted characters??

Thanks

Michelle

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2011-05-09T20:39:41+00:00

    Replace "," with nothing (ctrl/H)

    100+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2011-05-09T20:45:31+00:00

    To remove ALL of the commas and full stops, you can use Find/Replace

    To remove commas and full stops if, and only if, they occur at the end of the string, you will need a formula or a macro.

    For example, in an adjacent column, with your string in A1:

    =IF(OR(RIGHT(TRIM(A1),1)={",","."}),LEFT(TRIM(A1),LEN(TRIM(A1))-1),TRIM(A1))

    Fill down as far as required.  Then Copy | Paste Special/Values to get rid of the formulas.

    If that doesn't work, there may be nbsp's at the end of the string, and you may have to also remove them.

    40+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2011-05-09T20:55:40+00:00

    We examine the last character of each line and drop it if it is a comma or a space:

    With address data in column A, in another column enter:

    =IF(OR(RIGHT(A1,1)=",",RIGHT(A1,1)=" "),LEFT(A1,LEN(A1)-1),A1) and copy down

    2 people found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-10-10T05:19:10+00:00

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

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2015-07-11T21:30:41+00:00
    1. highlight the column or row
    2. Right click
    3. format cells
    4. choose "number"
    5. From negative numbers, select the option WITHOUT the negative
    6. voila

    2 people found this answer helpful.
    0 comments