FORTRAN format conversion

[Bung]

I have a whole lot of data derived from old FORTRAN punch cards. I have imported this into Excel, but I would like to convert the numbers from the Fortran F5.2 format into "proper" numbers. Some numbers have explicit decimal points given some don't - it's a bit of a mixture.

Does anybody have any ideas on how to do this, or is writing a macro or formula my only way out?

Bung
Life is non-linear...

 

[Bung]

Discovery #1 - you have to be careful to import the f5.2 fields as TEXT if you don't want to lose the explicit decimal points on numbers such as "98." because "general" format will just drop the point. Excel is very lazy about adhering strictly to types!

So I guess I'm stuck with using a formula - which I have already worked out. I was just hoping that there was a more elegant format conversion option, seeing Fortran has been around a while.

Bung
Life is non-linear...

 

[BillPSU]

Bung

What does your data look like? What do you want the final data to look like.

Bill

 

[Bung]

it looks like:

23123.9876234.546790

I want to convert that, given it is 4F5.2, into
231.23, 0.9876, 234.5, 467.90

Like I said, F5.2 to ordinary numbers in spreadsheet cells, one number per cell.



Bung
Life is non-linear...

 

[MortenA]

You could import everything into text cells and the write a small VB macro that would chunk every line up into 5 digit strings (using mid()) and then convert this into numbers.

Best regards

Morten

 

[joerd]

Don't even need VB, you can use Mid in a worksheet, like =Mid(A1,1,5) and =Mid(A1,6,5) etc.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IRstuff]

I'm confused.. If it's 4F5.2, all the numbers should have 2 decimal places. Isn't that what the "F5.2" command does?

TTFN

 

[BillPSU]

Bung
The following is a formula to convert the data into a good number however the data must be imported and Excel imports the data in one row not as a column which may present a problem to you.

=IF(ISERR(FIND(".",A1)>0),MID(A1,1,3)&"."&MID(A1,4,2),A1)

Bill

 

[MortenA]

Joerd: I dont think you got the point about there beeing more than 1 number pr line. Thats why i recommend VB - unless of course there allways the same number of digits pr line - then it could be done your way just as well.

Best regards

Morten

 

[Bung]

IRstuff, F5.2 implies two decimal places only if the point is not specifically included. So 50.79 is read the same as 5079 (right justified).

Everyone else, thanks for the formula and macro ideas, I've already done all that. I was just wondering if I had missed some esoteric function deep inside Excel that would do it without resorting to string manipulation. It seems not!

(You could of course get into a discussion about the usefulness of a number like .0001 when the rest of your data is expected to be of the scaled properly for a F5.2 input. But my data is scaled OK, its just that some of it was put in by a grey-bearded old Fortran purist, and some by a younger person who has never heard of Fortran).


Bung
Life is non-linear...