TEXT to COLUMNS

[F. Lawrence Kulchar]

I use the "text to columns" (data menu) feature to copy data I get from an
internet site onto my EXCEL spreadsheet.

To be more specific, I copy data for 1)the location, 2) the latitude, and 3)
the longitude into colukmn A ONLY:

A B C D E F
G

Jasper 52° 53' N 118° 4' W
52 53 118 4
Jasper 52.88 118.07

THEN, I USE TEXT TO COLUMNS TO 'SPREAD' MY DATA FROM COLUMN A INTO COLUMNS
A,B,C,D,E,F &G.

Then, my program converts degrees with minutes into a number with 2 decimal
places.

IT WORKS PERFECTLY...the first step in the "Text to Columns" wizard
indicates a FIXED WIDTH, which later permits me to eliminate columns so that
I can properly align my data.


HERE IS THE DILEMMA: When I repeat my copying and pasting further DOWN my
spreadsheet, the "TEXT TO COLUMNS" step 1 wizard is indicating "Delimited"
instead of "Fixed-width"...thus creating additional work on my part.

I CANNOT FIGURE IT OUT!!!!!!!!!!!!!!!!!! WHY SHOULD MY EXACT SAME TEXT TO
COLUMNS FEATURE GIVE ME 2 SEPARATE OPTIONS ON THE SAME WORKSHEET????

1...fixed width AND
2...delimited

depending on whether I am working HIGH in the worksheet, or, further down
thw page??

PLEASE EXPLAIN?

Thank you,

FLKulchar

 

[Earl Kiosterud]

F,

Try using paste special instead of paste to put the data into Excel. You may find a
selection, depending on the nature of the data, that takes care of this.

 

[Shane Devenshire]

If the paste special command does not correct the issue you could record a
macro to do the Text To Columns command and then all future commands would
be one step if you assigned a shortcut key.

Here is sample code:
Sub TtoC()
' Keyboard Shortcut: Ctrl+Shift+C
Selection.TextToColumns _
Destination:=ActiveCell, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(10, 1), Array(14,
1), Array(16, 1), _
Array(21, 1), Array(24, 1))
End Sub


Cheers,
Shane Devenshire
Microsoft Excel MVP