Any Excel experts here?

The easiest way to do it is to just copy the formulas on sheet2 down for 100 rows or more. Then when you add data on sheet1 it will appear on sheet2 in the correct format.

Ya, I sort of ended up doing that. Two problems I found.

First when you copy the formula to empty slots it loads them with ZEROS, which I don't want because those 0s will show up in the CSV file. But chatting with a co-worker who is pretty slick with Excel, I was able to use an IF statement in the formula so that is the cells it is calculating on are empty, then the sheet2 field should be empty so no zeros show up. That fixed that.

But then I copied the formula all the way down the columns which is something like 65000 rows. So it made for a huge Excel file (20 megs), so I will have to go back in and gid rid of them and instead copy the formula down to whatever a reasonable number of rows I think I may need. Right now the POI is 500+ entries. I think I could do ok for a while with a couple thousand lines preloaded with the formula.

Thanks,
PT

p.s. In case anyone cares, I am starting a POI file of SUM ATMs starting with Massachusetts and New Hampshire. I may expand the range as I go or create additional ones for other areas. I wanted a SUM POI file that will show me any SUM banks regardless of who owns it. Didn't see one in here (most are bank specific) so started to create one. I'll post more details and requests for input later in the Projects area of these discussion groups.

Send me your file and I will write a macro to loop through sheet 1 and reformat to sheet 2. No formulas needs.
ebrennan (at) rgis.com

Hi Guttermouth,
I noticed you are on a Mac. Every time I created a
csv file with Excel on a Mac it had a double extension. Bent Biker told me about a free program call "NeoOffice" and I have been using it for a while.

It seemed to resolve the double extension issue.

There is a setting in NeoOffice to set the decimal as well. I set it at 5 so I don't get that extra zero.

I'm not sure if NeoOffice will resolve your issue's
but it has been working for me.

Let me see if I can prune it back down again to just sheet one and then the formula on one line of sheet2 so you can see what I am trying to do, then I will email it to you.

Thanks,
PT

Yes, actually at home I do use NeoOffice. In fact here is a past discussion we had about NeoOffice, Excel, and OpenOffice all having a bit of a "bug" (feature?) with truncating the decimal places when outputting a CSV file.

http://www.poi-factory.com/node/11495

Cheers,
PT

Thanks Guttermouth, I missed that one..

Is there a formula that will easily convert the following type of text?

11720 N 1st St
Abilene, TX 79603
(325) 672-1872

to

11720 N 1st St,
Abilene, Tx 79603,
(325) 672-1872

the difference being the comma after St and also after zip code.

mahoney

Is that all in a single cell ofyour spread sheet?

I sent my file to ebrennan (see above) and he whipped me up a kick-arse macro that takes my data from my first sheet and then converts it all to the format I wanted on sheet2. Works fantastic.

So you might consider sending the file to him as well and see what he can do for you.

Cheers,
PT

Hey!

The POI file has been started and is now posted on here at the POI-Factory...

http://www.poi-factory.com/node/13884

Thanks again for the assistance with the Excel Macro. I have given you honorable mention on the summary page linked above.

Cheers,
PT

If the complete cell entry only has the commas you want to get rid of, you can use a find & replace function located under the "Edit" menu and replace the "," with "blank" in that cell.

If the info is all in one cell with line breaks...Try opening the file in NOTEPAD and do a find/replace changing the line break character (alt-D option in Excel) with a ",*" (* stand in for line break character - square box)

ebrennan, could you attach a copy of your macro so others could see how you did it?
Thanks!

Where do you want me to place this file.

Please remember this macro was create to meet the need for Guttermouth and the way his original file was formatted. Each file might need a different macro.

Here is the source code.

'Name of the worksheets
Public Const st1 As String = "Imput"
Public Const st2 As String = "CSV_Format"

Sub CreateCSV()
'Created by Eric Brennan
'ebrennan@hotmail.com

'Set some Variables
Dim ImputCount As Long
Dim J As Long
Dim strAdd As String, strCity As String, stState As String, stZip As String

'Delete old CVS Data
Call DeleteOldCSV

'Find the last row of the Imput Tab
ImputCount = findLastRow(1, 1)

'Set Counter for second sheet
J = 1

Sheets(st2).Select
'Loop through all the row on the first sheet and add them to the second.
For i = 3 To ImputCount
Cells(i, 1).Select
strAdd = Sheets(st1).Cells(i, 2)
strCity = Sheets(st1).Cells(i, 3)
strState = UCase(Sheets(st1).Cells(i, 4))
strZip = FormatZip(Sheets(st1).Cells(i, 5))

Sheets(st2).Cells(J, 1) = Sheets(st1).Cells(i, 7) 'bg_long
Sheets(st2).Cells(J, 2) = Sheets(st1).Cells(i, 6) 'bg_Lat
Sheets(st2).Cells(J, 3) = Sheets(st1).Cells(i, 1) 'Name
Sheets(st2).Cells(J, 4) = strAdd & "," & strCity & "," & strState & "," & strZip

J = J + 1
Next i

Sheets(st1).Select
Cells(1, 1).Select

Sheets(st2).Select
Cells(1, 1).Select

MsgBox "Conversion Complete!"

End Sub

Sub DeleteOldCSV()
Sheets(st2).Select
Cells.Select
Selection.ClearContents
Range("A1").Select
End Sub

Function FormatZip(strZip As String) As String

If Len(strZip) < 5 Then
strZip = CStr(Right(CInt(strZip) + 100000, 5))
End If

FormatZip = strZip

End Function

Function findLastRow(btSheet As Byte, btColumn As Byte)
'Wrote this macro years ago but find it create to find the last row of any given sheet.

Sheets(btSheet).Select
Application.Goto Reference:="R65000C" & btColumn
Selection.End(xlUp).Select

findLastRow = ActiveCell.Row

End Function

And if it helps, my master data in the spread sheet that he wrote the Macro for had 7 columns. Name, Address, City, State, Zip, then two more for coordinates.

For the GPS, I needed that distilled down to 4 columns on sheet 2 which could then be outputted to a CSV file. The first two columns are the coordinates and they have to be in the reverse order than my master list. Then column three is the name, then column four would be the Address, City, state and Zip code all combined (aka the "description" field of a CSV file).

PT