TEXT to COLUMNS

  • Thread starter Thread starter F. Lawrence Kulchar
  • Start date Start date
F

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
 
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.
 
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
 
Back
Top