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

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

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