How to remove first comma in a value
 

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 ?

How to remove first comma in a value
 

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

"JohnBlack" wrote:

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 ?

How to remove first comma in a value
 

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)

--
Gary''s Student - gsnu200856


"JohnBlack" wrote:

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 ?

How to remove first comma in a value
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JohnBlack" wrote in message
...
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 ?

How to remove first comma in a value
 

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

"FSt1" wrote:

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

"JohnBlack" wrote:

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 ?

How to remove first comma in a value
 

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" wrote:

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

"FSt1" wrote:

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

"JohnBlack" wrote:

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 ?

How to remove first comma in a value
 

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

"Gary''s Student" wrote:

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)

--
Gary''s Student - gsnu200856


"JohnBlack" wrote:

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 ?

How to remove first comma in a value
 

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
---------------
Jacob Skaria


"JohnBlack" wrote:

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 ?

How to remove first comma in a value
 

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.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"JohnBlack" wrote:

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 ?