If found character insert a comma after it

Hello all,

I have the following data in a cell:

55°66'77.88"N122°33'44.55"W

I need to be able to change it to this:

55°66'77.88"N,122°33'44.55"W

(All GPS coords have Ns and Ws only).

So, if it finds the letter 'N', then insert a comma after it.

Same would apply for a cell with the following data in it:

12.345678°-123.456789°

The above should be displayed like this:

12.345678°,-123.456789°

Haven't been able to find the correct formula.

The closest one I have found so far is this:

=REPLACE(B2,FIND("N",B2),0,",")

But, I'm not replacing anything with anything, AND, it places a comma BEFORE the 'N' instead of AFTER it.

Thank you in advance.

--Nelson.

Originally Posted by OllyXLS

One formula to do both:

Formula:

=SUBSTITUTE(SUBSTITUTE(B2,"°","°,"),"N","N,")

Thank you OllyXLS.

I'll use =SUBSTITUTE(B2,"N","N,") only in the case of Ns since yours will produce something like this:

36°,49'38.12"N,119°,33'36.40"W

Other than that that works out perfectly. Simple enough I would say!

Now, how about the other scenario where there's only degrees:

12.345678°-123.456789°

If I use =SUBSTITUTE(B4,"°","°,") then it will show the following:

12.345678°,-123.456789°, (no comma needed at the end).


Thank you again.

--Nelson.

Originally Posted by OllyXLS

How about

Formula:

=LEFT(SUBSTITUTE(B4,"°","°,"),LEN(B4)+1)

Awesome OllyXLS. Thank you very much.

--Nelson.

Originally Posted by arlu1201

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

Ok. Thank you.

--Nelson.