We have a customer at work who wants us to start billing in a 500
character edi format.

What that means is basically we need to take our bill and create a 500
character long string from each line.

Most of the characters in this "string" do not change however the
billing code, description, labor rate, material charge, etc will
alternate.

What I need to do is use our existing spreadsheet because the format is
used for our billing system and have another spreadsheet that pulls in
the work code, description, costs, etc and throw it in place with the
500 character line, merge it and kick it out in ascii.

Here is a small "dummy" example with the fields that I would need to
pull in highlighted in red:

1GATX12050562478640800905EINTERIORRUBBERLINING0000 000000000000000047290018500S000950RR0000000000000


I hope I explained it well enough. Is this even possible? Thanks in
advance for your input.


--
denileigh
------------------------------------------------------------------------
denileigh's Profile: http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=498927

On Fri, 6 Jan 2006 19:55:35 -0600, denileigh
wrote:


We have a customer at work who wants us to start billing in a 500
character edi format.

What that means is basically we need to take our bill and create a 500
character long string from each line.

Most of the characters in this "string" do not change however the
billing code, description, labor rate, material charge, etc will
alternate.

What I need to do is use our existing spreadsheet because the format is
used for our billing system and have another spreadsheet that pulls in
the work code, description, costs, etc and throw it in place with the
500 character line, merge it and kick it out in ascii.

Here is a small "dummy" example with the fields that I would need to
pull in highlighted in red:

1GATX12050562478640800905EINTERIORRUBBERLINING000 0000000000000000047290018500S000950RR0000000000000


I hope I explained it well enough. Is this even possible? Thanks in
advance for your input.

The data you need to extract (or insert) should either always be in the same
location in the string, or they should have defined delimiters.

You can use the MID function to build up (or extract) the appropriate string
segments. If you are working in VBA, you might also want to look at the MID
statement.
--ron

Sure it is possible but you need to learn how to write to a text file. Here
is a sample bit of code to get you started...

Dim MyIndex, FileNumber
For MyIndex = 1 To 5 ' Loop 5 times.
FileNumber = FreeFile ' Get unused file
' number.
Open "TEST" & MyIndex For Output As #FileNumber ' Create file name.
Write #FileNumber, "This is a sample." ' Output text.
Close #FileNumber ' Close file.
Next MyIndex


--
HTH...

Jim Thomlinson


"denileigh" wrote:


We have a customer at work who wants us to start billing in a 500
character edi format.

What that means is basically we need to take our bill and create a 500
character long string from each line.

Most of the characters in this "string" do not change however the
billing code, description, labor rate, material charge, etc will
alternate.

What I need to do is use our existing spreadsheet because the format is
used for our billing system and have another spreadsheet that pulls in
the work code, description, costs, etc and throw it in place with the
500 character line, merge it and kick it out in ascii.

Here is a small "dummy" example with the fields that I would need to
pull in highlighted in red:

1GATX12050562478640800905EINTERIORRUBBERLINING0000 000000000000000047290018500S000950RR0000000000000


I hope I explained it well enough. Is this even possible? Thanks in
advance for your input.


--
denileigh
------------------------------------------------------------------------
denileigh's Profile: http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=498927

but I have a couple more questions......I need to auto add the date in a
field like this mmddyy with no slashes.....how do I format that?

Also...the labor field HAS to be 7 characters long so for example
$50.23 needs to be formated as 0005023. Is there a way for have it
figure the amount and format the outcome like that?

Thanks for the help!


--
denileigh
------------------------------------------------------------------------
denileigh's Profile: http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=498927

Excel doesn't have an autoformat to convert your number into a date.

You have to type it in as a date (including slashes), then the format will
display the date the way you like.

But if want a macro to do the work for you, you could use Chip Pearson's:
http://cpearson.com/excel/DateTimeEntry.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

====
And maybe you could use a helper cell that formats the $50.23 the way you like:

=TEXT(A1*100,"0000000")




denileigh wrote:

but I have a couple more questions......I need to auto add the date in a
field like this mmddyy with no slashes.....how do I format that?

Also...the labor field HAS to be 7 characters long so for example
$50.23 needs to be formated as 0005023. Is there a way for have it
figure the amount and format the outcome like that?

Thanks for the help!

--
denileigh
------------------------------------------------------------------------
denileigh's Profile: http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=498927

--

Dave Peterson

"denileigh" wrote
in message ...

but I have a couple more questions......I need to auto add the date in a
field like this mmddyy with no slashes.....how do I format that?

Also...the labor field HAS to be 7 characters long so for example
$50.23 needs to be formated as 0005023. Is there a way for have it
figure the amount and format the outcome like that?

Thanks for the help!


--
denileigh
------------------------------------------------------------------------
denileigh's Profile:
http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=498927



For a fixed field, of say 10 characters, and you need to 0 fill the left
side, for example, if $125.50 needs to be 0000012550, you could do something
like

put 125.50 in A1

then in B2 use =right("0000000000"&(a1*100),10)

_______________

For a date, you could do something like

put a date in A3. say 12/25/2005

then if your date format was yyyymm you could do something like

b3 =Year(a3)&(right("00"&month(a3),2))

then to tie them all together, a concatinate formula might work

Thanks so much for all your help! I am really making HUGE progress on
this!!!!

Can anyone help me format the date like this? yymm? I have searched
the help files and can't figure that one out!


--
denileigh
------------------------------------------------------------------------
denileigh's Profile: http://www.excelforum.com/member.php...o&userid=30211
View this thread: http://www.excelforum.com/showthread...hreadid=498927