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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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:
Upvote 0
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 by a moderator:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top