(EXCEL VBA) How to remove commas and spaces from txt output?

1.5k views Asked by At

I have an excel file that looks like this:

3001 T81 90300010 001

3001 T81 90300011 001

and the issue is that some of the numbers are numbers stored as txt.

Also there are strings: "T81"

I need the outputted txt file to look like this:

3001T8190300010001

No Spaces, quotes, etc.

I was able to run this script to do most of the legwork:

Dim myFile As String, rng As Range, cellValue As Variant, I As Integer, j As Integer

myFile = Application.DefaultFilePath & "\test_data_output.txt"
Set rng = Selection

Open myFile For Output As #1

For I = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count

cellValue = rng.Cells(I, j).Value

If j = rng.Columns.Count Then
    Write #1, cellValue
Else
    Write #1, cellValue,
End If

    Next j
Next I

Close #1

Now its output is not quite perfect:

3001,"T81",90300010,"001"

3001,"T81","90300011","001"

2

There are 2 answers

3
TMH8885 On BEST ANSWER

In the line where you set cellValue, just keep adding the strings together (converting value to strings):

cellValue = cellValue + Cstr(rng.Cells(I, j).Value)

Then between your next j and next I, reset cellValue:

    Next j
    cellValue = ""
Next I
3
Excel Developers On
Dim v as Variant

v = rng.Value

For i = LBound(v,1) to UBound(v,1)
    Write #1, Replace(Replace(Join(WorksheetFunction.Index(v,i,0),""),",","")," ","")
Next