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,958
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,958
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,452
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,958
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,085,797
Messages
5,385,954
Members
401,977
Latest member
AdanRamos

Some videos you may like

This Week's Hot Topics

Top