Fixed width

  • Thread starter Thread starter TP
  • Start date Start date
T

TP

When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed
Width criteria and PRE-assigns the width.
In most of the cases I work with Excel is wrong 99% of the time.
Is there a way to force Excel to NOT pre-assign the width (leave everything
blank)?
The data I would like to parse is in the following format:

INTEGER HEXADECIMAL_NUMBER DATE TIME TEXT, TEXT, TEXT, TEXT,
TEXT, TEXT, TEXT, TEXT, TEXT, TEXT

The first 4 fields are fixed width and the subsequent fields are different
length but comma delimited (with space after the coma).
I need to keep all of the TEXT together and separate, apply Text-To-Columns,
the first 4 fields.
I would like to do this in one step rather than two.
If I use a delimiter, space, then everything will be separated. If I use
comma then the first three fields will remain together.
If I use "Fixed Width" Excel decides to separate the TEXT as well (and I
have to go through the exercise of removing the separations.
I would prefer to have Excel not "guess" where the data should be parsed, so
that I can enter my own separations.
Is this possible?
 
I don't think you can touch how excel guesses the parsing of your data.

But if your data is always laid out the same way, you could use a macro that
does the parsing exactly the way you want.

Just record a macro when you do it once manually and you'll have the code.

Saved from a previous post:

I like to create a dedicated macro workbook that contains the code. And then I
put a big button from the Forms toolbar on the only worksheet in that workbook.
I'll add a few instructions to that sheet, too.

Then I can distribute this macro workbook to other users (and use it myself) so
that I can just click the giant button to invoke the macro that imports the text
file.

I'd tweak the code to get the name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Thank you for the response. It answers my question.
Also, unfortunately, a macro will not work.
The length of TEXT in the original data is never the same.
So, I am stuck with doing a Text-To-Columns twice or clearing the extra
parsing by Excel every time.
 
This macro appears to do what you want...

Sub SplitData()
Dim Cell As Range, ModifiedText As String, S() As String
For Each Cell In Selection
ModifiedText = Replace(WorksheetFunction.Trim(Replace( _
Cell.Value, ", ", ",")), " ", ",", , 4)
S = Split(ModifiedText, ",")
Cell.Resize(1, UBound(S) + 1) = S
Next
End Sub
 
Back
Top