Excel to text with variable delimiters

[ajelcick]

Hello everyone. After digging around through various codes and tutorials and attempting to recall the last formal instruction on VBA I had in the late 90's, I still cannot seem to figure out if a VBA macro in excel can do what I need.

First off, I'm using Excel 2007 with VBA 6.5

That being said, I have an excel spreadsheet with a selection of data I would like to output to a text file, the catch is that the delimiter of the data changes from line to line in order to fit the expectations of the second program I'm feeding the data into. Additionally, the input excel file has data organized in columns, but that data will need to be transposed into a single delimited row.

The best idea I have would be a line by line read and output with its own delimiter, but I'm unsure how to go at this from a line by line standpoint.

Rather than describing by exact input and output parameters and begging for code, If someone could give me an outline or point me in the right direction so I can try and learn this myself, I would greatly appreciate it.

 

[hiker95]

ajelcick,

Welcome to the MrExcel forum.

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[mikerickson]

You could use UDF. The one below will cocatenate a range of strings that meet specified requirements.

Suppose you have Name, Address and Phone columns in A:C

put the formula =ConcatIf(A1:C1, "<>", A1:C1, ",") in E1 and drag down.
Then each cell in E has a comma delimited string of the data for that row.

To take each of those and create a semi-colon delimited string, put
=ConcatIf(E:E, "<>", E:E, ";") in a cell.

Some approach like that should work for you.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                            stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

 

[ajelcick]

Thanks for the quick replies. Here is the format of the Input XLS with some sample data (without unused rows/columns:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N
3	1	2	3	4	5	6	7	8	9	10	11	12
4	-0.01633	0.012667	-0.01033	0.014667	0.006667	0.018667	0.017667	-0.00533	-0.00033	0.005667	-0.00933	-0.03433
5	-0.05333	0.986667	0.792667	1.652667	1.434667	1.398667	1.702667	1.645667	1.393667	1.742667	1.650667	0.040667
6	-0.00933	0.614667	0.410667	1.366667	1.068667	1.243667	1.278667	1.407667	1.304667	1.553667	1.472667	0.010667
7	-0.02133	0.399667	0.258667	1.119667	1.004667	0.805667	0.883667	1.091667	1.000667	1.187667	1.170667	0.019667
8	-0.05933	0.183667	0.143667	0.756667	0.572667	0.462667	0.552667	0.700667	0.723667	0.874667	0.784667	-0.00433
9	-0.03633	0.060667	0.048667	0.345667	0.317667	0.272667	0.304667	0.407667	0.405667	0.537667	0.432667	-0.00133
10	-0.05433	0.024667	0.008667	0.202667	0.161667	0.121667	0.148667	0.169667	0.172667	0.304667	0.251667	-0.01133
11	-0.04333	-0.00733	-0.02233	0.129667	0.061667	0.060667	0.084667	0.101667	0.123667	0.161667	0.142667	0.008667
370 tet p12 092311.pda

Excel 2007


Here is the desired output format with cell names used and required spacers indicated with []:

[space]A1[,][space]B1[,][']C1[,][']D1[,][']E1
[']F1
[']G1
[space x6][A2][,][space x6]B2[,][space x6]C2[,][space x6]<- REPEAT TO K2
[space]C4[,][space]C5[,][space]C6<-- REPEAT TO C11
[space]D4[,][space]D5[,][space]D6<-- REPEAT TO D11
[space]E4[,][space]E5[,][space]E6<-- REPEAT TO D11
[space]F4[,][space]F5[,][space]F6<-- REPEAT TO D11
[space]G4[,][space]G5[,][space]G6<-- REPEAT TO D11

 

[mikerickson]

Try
=" "&A1&", ""&B1&",""&B1&",""&D1&",""&E1 in AA1
="'"&F1 in AA2
="""&G1 in AA3
=Rept(" ",6) & ConcatIf(A2:K2,"<>",A2:K2,","&Rept(" ",6)) & ","&Rept(" ",6) in AA4
=" " & ConcatIf(C4:C11,"<>",A2:K2,", ") in AB5 and drag right
Put the array formula =TRANSPOSE(AB5:ZZ5) in AA4:AA(many)
Column AA should be your output column