Changing txt input into a modified orientation.

[Guest]

In short, I need to export asci txt from one program to another. Of course
the formats
are slightly different. The exported variables are aligned vertically while
the input program only recognizes the variables in line horizontally. Ideally
I had hoped excel could alter this discrepency automatically. The layouts are
as follows:

Exported format:
34.07015825, -118.25707907, ">1 450 ft", "R01>01", "", "Rte/1", ff0000
34.05852393, -118.22487060, ">2 450 ft", "R01>02", "", "Rte/1", ff0000

Required/converted Input orientation:
1 = 34.07015825, -118.25707907, 34.05852393, -118.22487060, 450,

These are geo coordinates derived from a mapping program to be used in a
flight management system. The examples represent two waypoints in decimal
degrees that constitute the beginning and end of a line. The export
coordinates are arranged vertically followed by relative waypoint info (gnd
elev, route/line#,and wpt#), Input only recognizes the lat/long coords
preceeded by the line designation (Rte/1). the ground elev helps but not
necessary.

Can this be done fairly easily without significant formulas? I am fairly
well versed in general applications but never had time for programming etc.
If anyone can point me in the right direction I will attempt to return the
favor someday.

Thank you,
Jim (Traveller)

 

[Biff]

Hi!

Can this be done fairly easily without significant formulas?

Depends on how you define "easily" and "significant" !!!!!

This works based on your limited sample:

=IF(MOD(ROW(A1),2)=0,"",(LEFT(A1,FIND(">",A1)-2))&LEFT(A2,FIND(">",A2)-2)&MID(A1,FIND(">",A1)+3,(FIND("ft",A1)-1)-(FIND(">",A1)+3)))

Assumptions:

Each line is in a separate cell:

A1 = 34.07015825, -118.25707907, ">1 450 ft", "R01>01", "", "Rte/1", ff0000
A2 = 34.05852393, -118.22487060, ">2 450 ft", "R01>02", "", "Rte/1", ff0000

The range that contains this data is contiguous.

The related data is in groups of 2 cells. E.G. - A1&A2, A3&A4, A5&A6

The first part of the formula:

=IF(MOD(ROW(A1),2)=0,""

Is used to "ignore" the 2nd related cell so that output is generated to a
single cell. Depending on what row number the data actually starts on you
may have to modify the MOD function and reverse the argument order in the IF
function.

Post back if you need further assistance in doing that.

Biff