The opposite: Delimited to formatted text

uhrzeit

New Member
Joined
Sep 25, 2019
Messages
1
Hello,
Text to Delimited text seems to be common, but not the other way around.
I have an excel table with thousands of rows, all neatly organized in a rational manner. all cells are used with no spaces. i need to output data into a text file for another computer program's input. It needs to be in a standard txt file format with spaces in an odd manner.
  • A single space is present in the first column
  • A total of 8 single spaces including the first column separate number values
  • the next 28 numbers are followed by double spaces in between values

I have copied 1 plain text row as shown below for illustration purposes.

if anyone knows a simple way of using the correct format by either copy/pasting into a new workbook OR some other function within excel, please let me know.
thank you

01 01 77 01 -0.855E+01 -0.114E+02 -0.113E+02 -0.106E+01 0.923E+00 0.244E+01 0.000E+00 0.000E+00 0.000E+00 0.865E+00 0.103E+04 0.000E+00 0.000E+00 0.000E+00 0.000E+00 0.000E+00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,647
Office Version
365
Platform
Windows
Is this what you want ?

8 single spaces
{s}1{s}1{s}77{s}1{s}-8.55{s}-11.4{s}-11.3{s}
followed by double spaces and ending without a space
-1.06{ss}0.923{ss}2.44{ss}0{ss}0{ss}0{ss}0.865{ss}1030{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}0{ss}11

Here is a function which does that - place in a STANDARD module
Code:
Function GetString(aCell As Range) As String
    Dim c As Long, aString As String, Spaces As String
    aString = ""
    Const S = " "
    Const SS = "  "
    aString = S
    For c = 0 To 36
        Select Case c
            Case Is < 7
                Spaces = S
            Case Is < 36
                Spaces = SS
        End Select
        aString = aString & aCell.Offset(, c).Value & Spaces
        Spaces = ""
    Next c
    Debug.Print aString
End Function
You could use as a formula in AL2 copied down
=GetString(A2)

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
1
2
1​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
11​
1 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11
3
2​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
12​
2 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12
4
3​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
13​
3 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 13
5
4​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
14​
4 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 14
6
Sheet: Data


Or call in VBA like this
Code:
Sub uhrzeit()
    Dim Cel As Range, Rng As Range, ws As Worksheet
    With Sheets("Data")
        Set Rng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
    Set ws = Sheets.Add
    ws.Cells(1).EntireColumn.ColumnWidth = 150
    For Each Cel In Rng
        ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) = GetString(Cel)
    Next Cel
End Sub
Result

Excel 2016 (Windows) 32 bit
A
B
1
2
1 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11
3
2 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12
4
3 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 13
5
4 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 14
6
Sheet: Sheet17
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,647
Office Version
365
Platform
Windows
This looks better I hope :confused:

A:AK

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
1
2
1​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
11​
3
2​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
12​
4
3​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
13​
5
4​
1​
77​
1​
-8.55​
-11.4​
-11.3​
-1.06​
0.923​
2.44​
0​
0​
0​
0.865​
1030​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
0​
14​
6
Sheet: Data

Column AL

Excel 2016 (Windows) 32 bit
AL
1
2
1 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 11
3
2 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12
4
3 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 13
5
4 1 77 1 -8.55 -11.4 -11.3 -1.06 0.923 2.44 0 0 0 0.865 1030 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 14
6
Sheet: Data
 
Last edited:

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
I gave it a shot.

https://1drv.ms/u/s!Anxrq_k7ozqailKpT0MVVr_lMywZ?e=ESAlSn

all neatly organized in a rational manner
...we found one!

Code:
Sub RunThis()    
    Example ActiveSheet.UsedRange
End Sub


Sub Example(Target As Range)
    Dim a(), i, j, k, t, fn As String
    a() = Target
    fn = Application.GetSaveAsFilename(, "Text Files (*.txt), *.txt")
    If fn = "False" Then Exit Sub
    Open fn For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    For i = 1 To UBound(a, 1)
        For j = 1 To 8
            t = t & " " & a(i, j)
        Next
        For k = 9 To 36
            t = t & "  " & a(i, k)
        Next
        t = t & vbCrLf
        Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , t
        t = ""
    Next
    Close #1
End Sub
Sorry Yongle. Didn't mean to post over you. There were zero replies when I started on this. :rolleyes:
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
Another way would be
Code:
=" "&TEXTJOIN(" ",0,A1:H1)&"  "&TEXTJOIN("  ",0,I1:AK1)
then copy and pastespecial values.

You might need to adjust the column references slightly to fit your data, following the pattern that you describe, it appears that there are 2 extra numbers in the sample?

Note that this formula requires Excel 2019 or Office 365, it will not work with older versions.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,647
Office Version
365
Platform
Windows
Sorry Yongle. Didn't mean to post over you. There were zero replies when I started on this.
@Tom Schreiner

- no one has a monopoly here
- it is more interesting for everyone when there are several working solutions posted solving the same problem
- your solution takes things to the next stage and is superior to mine (y)
 

Forum statistics

Threads
1,081,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top