Remove commas at end of line only

M

Mark

Hi,

I have a column which contains part of an address eg.
"Honeyhouse, High Street,"

Is it possible to remove the comma at the end of the line but not the other comma?

Mark
 
D

Don Guillett

how about
sub removeendcomma()
for each c in selection
c.value=left(c,len(c)-1)

next
end sub
 
T

Ture Magnusson

Mark,

If your addresspart is in cell A1, use this formula in B1:

=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

If the addresspart may contain unwanted leading or trailing
spaces, use this instead:

=IF(RIGHT(TRIM(A1),1)=",",LEFT(TRIM(A1),LEN(TRIM(A1))-1),TRIM(A1))
 
E

Edward Stansfeld

How about trying Text to Columns, with a comma delimiter. You'd then have to
reassemble the addresses with =CONCATENATE(cell," ",cell,...etc)
Edward
 
P

Peter Atherton

Mark

Use a Helper column and enter this formula

=REPLACE(A9,LEN(A9),1,22)

Copy and pastespecial as values to remove the fomula

Regards
Peter
 
G

George Nicholson

Mark:

Here are 3 approaches, confined to standard worksheet fuctions.

Note: For the sake of clarity I have used CHAR(44) for "," (a comma in
quotes) and CHAR(32) for " " (a space within quotes) in all examples. They
are equivalent, just less confusing than a lot of quote marks and commas.
You might want to use "" (an empty string) in place of CHAR(32). I've
momentarily forgotten whether there is a CHAR() code for an empty string but
I don't think there is. I dare say someone will correct me if I'm wrong :)

Also: In the first 2 examples, if leading or trailing spaces might be an
issue, use TRIM(A1) rather than A1.

If the last character in the string is a comma, this will return that string
without the last character, otherwise it returns an unchanged string:
=IF(RIGHT(A1, 1) = CHAR(44), LEFT(A1, LEN(A1) -1), A1)

If the last character in the string is a comma, this will change that
character to a space, otherwise it returns an unchanged string:
=IF(RIGHT(A1, 1) = CHAR(44), REPLACE(A1, LEN(A1), 1, CHAR(32)), A1)

This will change the 2nd occurance of a comma within a string (if there is
one) to a space, otherwise it returns an unchanged string:
=SUBSTITUTE(A1, CHAR(44), CHAR(32), 2)
Note: this assumes that the offending end-of-line comma, if it occurs, will
*always* be the 2nd occurance and *only* the 2nd occurance (not the 1st, not
the 3rd).
This would be my last choice for that reason.

Hope this helps,
 
M

Mike

-----Original Message-----
Hi,

I have a column which contains part of an address eg.
"Honeyhouse, High Street,"

Is it possible to remove the comma at the end of the line but not the other comma?

Mark
.
Click to expand...
Try this,
1. SELECT the entire column containing the address string.
2. FIND/REPLACE the first comma AND THE SPACE after it
with a unique character, such as * or ?, etc.
3. now FIND/REPLACE the second comma with nothing (null).
4. now go back and FIND/REPLACE your unique character
with the original comma.
 
Top