The opposite: Delimited to formatted text

[uhrzeit]

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]

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

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][th]

Q​

[/th][th]

R​

[/th][th]

S​

[/th][th]

T​

[/th][th]

U​

[/th][th]

V​

[/th][th]

W​

[/th][th]

X​

[/th][th]

Y​

[/th][th]

Z​

[/th][th]

AA​

[/th][th]

AB​

[/th][th]

AC​

[/th][th]

AD​

[/th][th]

AE​

[/th][th]

AF​

[/th][th]

AG​

[/th][th]

AH​

[/th][th]

AI​

[/th][th]

AJ​

[/th][th]

AK​

[/th][th]

AL​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

11​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

12​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

3​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

13​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

4​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

14​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]


Or call in VBA like this

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td] 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[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td] 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[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td] 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[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td] 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[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet17[/td][/tr][/table]

 

[Yongle]

This looks better I hope

A:AK

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][th]

Q​

[/th][th]

R​

[/th][th]

S​

[/th][th]

T​

[/th][th]

U​

[/th][th]

V​

[/th][th]

W​

[/th][th]

X​

[/th][th]

Y​

[/th][th]

Z​

[/th][th]

AA​

[/th][th]

AB​

[/th][th]

AC​

[/th][th]

AD​

[/th][th]

AE​

[/th][th]

AF​

[/th][th]

AG​

[/th][th]

AH​

[/th][th]

AI​

[/th][th]

AJ​

[/th][th]

AK​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

3​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

13​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

4​

[/td][td]

1​

[/td][td]

77​

[/td][td]

1​

[/td][td]

-8.55​

[/td][td]

-11.4​

[/td][td]

-11.3​

[/td][td]

-1.06​

[/td][td]

0.923​

[/td][td]

2.44​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0.865​

[/td][td]

1030​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

14​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]

Column AL

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

AL​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td] 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[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]

 

[Legacy 98055]

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!

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.

 

[jasonb75]

Another way would be

=" "&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]

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

 

[Legacy 98055]

Dongle, thank you for your kind words.