fixed width file?

[kwiklearner]

I have a worksheet that I need to transform to a fixed width file.
Current data set includes:
10
-25.2
25.33
etc.
I need to convert the . to , and then ensure it is in the following format:
+0000000010,0000000
-0000000025,2000000

currently, I am using loop to review every row
ie. if sign(activecell)<0 then "-" else "+"
but I'm stuck on the 0 padding and 0 padding at the end.
Any help will be greatly appreciated.

[AA2e72E]

You need to learn the VBA Format command: see the help files.

"kwiklearner" wrote:

>
> I have a worksheet that I need to transform to a fixed width file.
> Current data set includes:
> 10
> -25.2
> 25.33
> etc.
> I need to convert the . to , and then ensure it is in the following
> format:
> +0000000010,0000000
> -0000000025,2000000
>
> currently, I am using loop to review every row
> ie. if sign(activecell)<0 then "-" else "+"
> but I'm stuck on the 0 padding and 0 padding at the end.
> Any help will be greatly appreciated.
>
>
> --
> kwiklearner
> ------------------------------------------------------------------------
> kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
> View this thread: http://www.excelforum.com/showthread...hreadid=521908
>
>

[Dave Peterson]

I'd use a helper column with a formula like:

=SUBSTITUTE(TEXT(A1,"[<0]-0000000000.0000000;[>=0]+0000000000.0000000"),".",",")

Then I'd copy that column and paste into NotePad. And save it as that text file
from Notepad.



kwiklearner wrote:
>
> I have a worksheet that I need to transform to a fixed width file.
> Current data set includes:
> 10
> -25.2
> 25.33
> etc.
> I need to convert the . to , and then ensure it is in the following
> format:
> +0000000010,0000000
> -0000000025,2000000
>
> currently, I am using loop to review every row
> ie. if sign(activecell)<0 then "-" else "+"
> but I'm stuck on the 0 padding and 0 padding at the end.
> Any help will be greatly appreciated.
>
> --
> kwiklearner
> ------------------------------------------------------------------------
> kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
> View this thread: http://www.excelforum.com/showthread...hreadid=521908

--

Dave Peterson