0

I have many files containing such lines :

  1. HUIHOJ OPKKA LK
  2. ASOIJS AISJJ PL
  3. AOSKSI ASIJD YA

I want to convert theses lines into something like this :

  1. HUI;HOJ ;OPKKA ;L;K
  2. ASO;IJS ;AISJJ ;P;L
  3. AOS;KSI ;ASIJD ;Y;A

So the first field would be 3 characters, second would be 4, third 6, fourth 1 and fifth 1 character.

I know that it's possible to do it manually with excel, but I need to have automatically conversion solution, because I have many files with exactly the same structure.

Community
  • 1
  • 1
baboufight
  • 133
  • 1
  • 1
  • 10

3 Answers3

2

VBA has a simple command to format text, so you can do this fairly easily, and with just a single line of code

Function SpFmt(S As String) As String
    SpFmt = Format(S, "@@@\;@@@@\;@@@@@@\;@\;@")
End Function

If you want to use a worksheet function, you can do this with a nested replace formula on the worksheet:

=REPLACE(REPLACE(REPLACE(REPLACE(A1,4,0,";"),9,0,";"),16,0,";"),18,0,";")
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Interesting - I didn't realize you could use REPLACE to just insert a string like that, by using '0' for the length being replaced. – Grade 'Eh' Bacon Nov 19 '15 at 16:38
0

Use the VBA Join Function after splitting (Split function) on a space or simply replace (Replace functoin) all of the spaces with a space & semi-colon.

dim str as string

str = range("A1").value2   'HUIHOJ OPKKA LK
range("A1") = Join(Split(str, char(32)), chr(32) & chr(59))  'HUIHOJ ;OPKKA ;LK

str = range("A1").value2   'HUIHOJ OPKKA LK
range("A1") = Replace(str, chr(32), chr(32) & chr(59))  'HUIHOJ ;OPKKA ;LK
0

If you are not bounded to Excel, you can use unix tools (also available for Windows) to do this very efficiently with just one command:

cut --output-delimiter=";" -c 1-3,4-7,8-13,14,15 fixed.txt > delimited.csv

The same command in a loop:

for f in *.txt ; do
  cut --output-delimiter=";" -c 1-3,4-7,8-13,14,15 "${f}" > "${f}.csv"
done

Edit : the output delimiter option does not seem to work on every platform.

Alternatively, you can use sed :

sed "s/^\(.\{3\}\)\(.\{4\}\)\(.\{6\}\)\(.\)\(.\)/\1;\2;\3;\4;\5/" fixed.txt > delimited.csv
Eric Citaire
  • 4,355
  • 1
  • 29
  • 49
  • Thanks! I'm on osx it will be perfect. I tried your command on a file named fixed.txt, but I get an error : "cut: illegal option -- - usage: cut -b list [-n] [file ...] cut -c list [file ...] cut -f list [-s] [-d delim] [file ...]" – baboufight Nov 19 '15 at 13:20
  • Ok, the osx version of cut may have slightly different options than on Linux. It doesn't seem to support the --output-delimiter option. You should refer to [Apple documentation](https://developer.apple.com/library/mac/documentation/Darwin/Reference/ManPages/man1/cut.1.html). – Eric Citaire Nov 19 '15 at 16:21
  • Same issue here : https://stackoverflow.com/questions/20195728/replacement-for-cut-output-delimiter. You may have to replace default output delimiter. – Eric Citaire Nov 19 '15 at 16:24