Insert double-comma at end of string?

S

spunk_of_mj

New Member
Joined
May 25, 2008
Messages
6
i'm a complete excel novice, but i think this should be pretty easy for you guys...

i just bought a new satnav system after 4 years with tomtom...

anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:

-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,

thing is, most of them don't have the 2 commas at the end and just end with the number, like this:

-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247



therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...

i'm guessing for anyone with excel experience, this should be pretty easy to solve...

thanks in advance!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Assuming that your strings are in column A starting at A1 then in B1

=IF(RIGHT(A1,2)<>",,",A1&",,",A1)

and fill the formula down.
 
Upvote 0
thanks for the swift reply, VoG II!

i should have said, the co-ordinates are actually listed downwards, ie. A1, A2, A3 etc...
 
Upvote 0
So does your sheet look like this?

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:185px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >-3.23956,51.52737,1,50,1,67</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >-5.29024,50.22664,1,50,1,67,,</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >-5.27461,50.23143,1,50,1,247</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
This is what I got using the formula I posted.

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:185px;" /><col style="width:21px;" /><col style="width:192px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >-3.23956,51.52737,1,50,1,67</td><td > </td><td >-3.23956,51.52737,1,50,1,67,,</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >-5.29024,50.22664,1,50,1,67,,</td><td > </td><td >-5.29024,50.22664,1,50,1,67,,</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >-5.27461,50.23143,1,50,1,247</td><td > </td><td >-5.27461,50.23143,1,50,1,247,,</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=IF(RIGHT<span style=' color:008000; '>(A1,2)</span><>",,",A1&",,",A1)</td></tr><tr><td >C2</td><td >=IF(RIGHT<span style=' color:008000; '>(A2,2)</span><>",,",A2&",,",A2)</td></tr><tr><td >C3</td><td >=IF(RIGHT<span style=' color:008000; '>(A3,2)</span><>",,",A3&",,",A3)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
excellent!

i will give it a bash and report back...

thank you so much for your time, VoG II - much, much appreciated... :)
 
Upvote 0
it works a treat, but unfortunately (for me), i'm unsure how to 'fill the formula down' and can only seem to get it to convert the very first cell...

can you assist me here?

thanks again...
 
Upvote 0
Click in the cell where you entered the formula then hover the cursor over the bottom right corner of the cell - the cursor should change appearance to a +

Now, hold down the left mouse button and drag the mouse down to copy the formula down as far as needed, then release the mouse button.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Runtime Error 462 when Running Code to Export Excel Values into Word Bookmarks
Replies
6
Views
948
pastelprincess
P
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A
G
  • Question
Help with writing VBA for data entry form
Replies
2
Views
789
GA036872
G
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
R
  • Question
Userform issue/challenge - Copying listbox selection to a sheet
Replies
0
Views
724
Ronie
R

Forum statistics

Threads
1,214,657
Messages
6,120,769
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top