Need to reformat/restructure flat data... excel, multiple rows into one row?

[cookiemunstahh]

Hi,

I need to run an analysis on data and the format my data needs to be in appears like this in excel (it has no actual file type). I have imaginary numbers.


Column 1              Column 2         Col 3           Col 4         Col 6
OrganismName     34234 + 3i     -345 - 4i    7345 + 1i   342 + 1i  (etc)   

In word it looks like this (ignore the data in the parenthesis)... the spaces between imaginary numbers are tabs but I can seem to replace spaces with TAB in word... TAB just moves the cursor to "FIND" rather than puts it in the "replace" box.

i  8 768
((((antennatum:0.087174,(divagans:0.058554,(pallidum:0.001320,traviatum:0.001320):0.057234):0.028620):0.032757,(daeckii:0.119931,(exsulans:0.061173,weewa:0.061173):0.058758):0):0.034601,basidens:0.154532)
antennatum   0 + 0i   0 + 0i   0 + 0i   -0.1193 - 0.3364i   0.298 + 0.029i   -0.0558 - 0.0819i   -0.0723 + 0i   0.2805 + 0i   0.7764 - 0i   0.1193 - 0.3364i   -0.298 + 0.029i   0.0558 - 0.0819i   0.0415 + 0.0632i   0.0542 - 0.0927i   -0.0066 + 0.0815i   -0.0503 + 0.0399i   -0.0518 + 0.0177i   0.0679 + 0.0792i

My data is in the format

Aca_gui20k_OL_2O_CS_15_0_reg.mat
    0.000000     0.000000i     0.000000     0.000000i     0.000000     0.000000i (it's on another line)
   -0.006809    -0.002711i     0.004239    -0.010468i     0.000643    -0.002013i
   -0.000135     0.000000i    -0.002218     0.000000i     0.029173    -0.000000i
    0.006809    -0.002711i    -0.004239    -0.010468i    -0.000643    etc.

In excel or works spreadsheet it appears as 6 columns of data when I need it to be written (replace (TAB) with TAB spaces since I can't show them here):

Aca_gui 0.000000 + 0.000000i(TAB)0.000000 + 0.000000i(TAB)0.000000 + 0.000000i(TAB)-0.006809 - 0.002711i(TAB), etc all on one line.

The data I have doesn't have the + between imaginary numbers... I was able to use Word, replace, etc but there are parts (at the end of lines that have spaces but no tab) that Word refuses to replace with commas (I was trying to make a csv) then convert it from there.

Anyone know how to do that?

[KB5cmd]

it's a bit difficult for readers to have to count/remember/ series of 0's and place them on the board so t speak. so if you could give an example with  more differentiated data, it would help to see where the stuff is. You could do this by multiplying all the values by the same set number, like, 10,000 ? 

Also for clarity, to be sure what we are dealing with, please load your spreadsheet in Excel, save it as a text file (.txt, or .csv) then list it on your post, using copy/paste on the whole data? Most likely you will see yourself that it can then be edited easily in Notepad this way. (I'd say, ...stay away from Word when just manipulating data!)