About Us

Dave Peterson[_3_]

That's the way I'd do it. (And I can't think of anything simpler.)

jeffP wrote:

Tom and Dave,
You are unbelievable in talent and help. I can't thank you enough.
I had one thing more. I need to take a cell that's 2 place numeric w/
decimal (1234.56) and move it to a cell formatted text, 9 characters (left
fill) without the decimal point (it would be assumed 2 decimal places). I
couldn't get it formatted without loosing the last two digits so this is
what I did. It works fine but was there a simpler way?
=text(sheet1!A1*100,""000000000"")

Thanks again for your time and help

--
jeffP

"Tom Ogilvy" wrote in message
...
=REPT(" ",20-len(TEXT(sheet1!a1,"000000.00"))) &
TEXT(sheet1!a1,"000000.00")

or, since we know the format will be 9 characters wide

=REPT(" ",11) & TEXT(sheet1!a1,"000000.00")

if you don't want leading zeros

with $

=REPT(" ",20-len(TEXT(sheet1!a1,"$#,###.00"))) &
TEXT(sheet1!a1,"$#,###.00")

Without $

=REPT(" ",20-len(TEXT(sheet1!a1,"#,###.00"))) & TEXT(sheet1!a1,"#,###.00")

or without $ and commas

=REPT(" ",20-len(TEXT(sheet1!a1,"#.00"))) & TEXT(sheet1!a1,"#.00")

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
Tom,
Great help from you and also from Dave. I still can't exactly get what
I
want , though. This gives me the perfect fixed length field that I need
for
all my fields except the $ amount field. For that field I adjusted
Dave's
suggestion =REPT(" ",4) & TEXT(A1,"000000.00")
to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it
formats
the
way I need but does not provide the fixed length.
I need the two decimal format but also the field filled left to 20
characters.
If you don't mind continuing to help, I always appreciate it.

--
jeffP

"Tom Ogilvy" wrote in message
...
=rept(" ",20-len(Sheet1!A1)) & Sheet1!A1

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
Tom,
With your help I'm getting this project to work. Thanks.
Adjust the formula if you want to left pad the value.<<<<<
I need to adjust the formula, but don't know how. As is it adds
spaces
to
the right of the existing number and I need it to fill on the left
of
the
number.
All your help is greatly appreciated.

--
jeffP

"Tom Ogilvy" wrote in message
...
select all you cells and set the font as courier new (a mono
spaced
font).
the autofit your columns or manually adjust the width.

Then do file=Save as and select Formatted Text (space delimited)
(.prn)

to be more exact, you might go to a second sheet and put in
formula
in
A1
like

=Sheet1!A1 & rept(" ",20-len(Sheet1!A1))

then dragfill down and across to capture your data.

Adjust the formula if you want to left pad the value.

then select all the cells and do Edit=copy, then Edit=Paste
Special
and
select values. This replaces the formulas.

then you can copy the sheet to a new workbook and save as above
after
autofitting the columns.

An alternative is to use low level file io to write out the file a
cell
at
a
time/a line at a time using space padding as above. Unix uses
vblf
as
a
line terminator I believe, while windows uses vbcrlf. I assume
Unix
will
be
able to handle the file, but if not, you will have to use code to
terminate
with vblf.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
Hi all,
I've got a project that takes a *dbf file into Excel, does some
clean
up
etc, then I need to save it as a fixed length text file that's
able
to
be
imported into a Unix system. I've got most of it done but now I
can't
get
it
exported or saved into a fixed field length text file.I've
worked
w/
comma
delimited files but this has me lost.
I have 8 columns that need to be set w/ fixed character lengths
even
though
they hold varying length data. Then I need to export/save the
sheet
as
a
text file for the Unix people.
As I mentioned , I'm really lost / stuck and any help is , as
always,
greatly appreciated.

--
jeff

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
fixed TextBox with Shrink to fit text	Yahya Al-Tawil	Excel Discussion (Misc queries)	1	April 21st 09 08:05 PM
convert fixed number to text.	john	Excel Discussion (Misc queries)	2	January 30th 09 09:22 PM
Macro - Fixed text code needs replacing with variable text	steven.holloway	Excel Discussion (Misc queries)	3	July 22nd 08 03:57 PM
FIXED TEXT AFTER DELETION	[email protected]	Excel Discussion (Misc queries)	3	November 22nd 06 02:32 PM

Menu

About Us