I don't know if this is possible to do but....

[denileigh]

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:

1GATX12050562478640800905EINTERIORRUBBERLINING0000000000000000000047290018500S000950RR0000000000000


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

[Ron Rosenfeld]

On Fri, 6 Jan 2006 19:55:35 -0600, denileigh
<[email protected]> 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:
>
>1GATX12050562478640800905EINTERIORRUBBERLINING0000000000000000000047290018500S000950RR0000000000000
>
>
>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

[Jim Thomlinson]

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:
>
> 1GATX12050562478640800905EINTERIORRUBBERLINING0000000000000000000047290018500S000950RR0000000000000
>
>
> 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
>
>

[denileigh]

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!

[Dave Peterson]

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

[Mbt6]

"denileigh" <[email protected]> wrote
in message news:[email protected]...
>
> 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

[denileigh]

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!