How to remove first comma in a value

[JohnBlack]

Dear all, I'm having a little trouble with an excel sheet. It consist of 500
plus records with values in it. Because the author of the document it was
copied from made some error with placing the decimals I'm now stuck with some
cells where there are multiple comma's such as:

78,583,506199
84,831,029277
87,756,172853
10,758,322459

I'm looking for a way to remove the first comma. Find and replace obviously
doesn't work because that will replace all the commas . Does anyone know of a
way to only remove the first comma ?

 

[FSt1]

hi
this may work.....assumeing that excel sees the data as text.
=REPLACE(B3,3,1,"")
assuming your data in column B.
add a column next to the data. copy the formula down as far as needed. the
copy the new column and paste special values. delete(or archive) old data.
if excel is not seeing the data as text you may be able to just refomat.
Post back if problems.
regards
FSt1

 

[Gary''s Student]

We can FIND the first comma and combine everything from the left of it to
everything from the right of it.

=LEFT(A1,FIND(",",A1)-1) & MID(A1,FIND(",",A1)+1,255)

 

[Ragdyer]

=REPLACE(A1,3,1,"")

 

[JohnBlack]

Wow, that was easy. Didn't know that function existed. Thanks FSt1.

 

[JohnBlack]

Actually your solution only works for the example values i gave. Not for all
tthe other values where the comma isn't the third character. I'm going to try
Gary''s Student solution.

 

[JohnBlack]

I tried your solution and it worked perfectly. Thanks a lot.

 

[Jacob Skaria]

Hi John

=REPLACE(A1,3,1,"") will work only if the comma is in the third position

Use the below formula with SUBSTITUTE() which will always remove the first
comma..in which ever position....

=SUBSTITUTE(A1,CHAR(44),,1)


If this post helps click Yes

 

[JohnBlack]

I tried you solution and this is the best solution. Gary''s Student's
solution was good but gave error on values without a comma. Your solution
just ignores the values without comma and just shows the original value.
Thanks a lot, all of you. You've been a great help.