Hi:

I have imported some data and the field look like:

847*30000*891*15000*848*2500*

What functions would I use so it looks like:

847: $300.00 | 891: $150.00 | 848: $25.00

Is it ALWAYS three pairs of values?

No, it can be from 1 to 7 pairs (although the most I have seen is 3).

Also some can have a minus in front of the number.

Here are some more examples:

847*30000*

847*30000*890*11000*849*5000*

503*348600*613*-348600*

597*-271973*

595*-8760*

503*401100*613*-401100*566*92070*

Note: There are no spaces in the data although some appear when I posted this

Edited September 5, 200817 yr by Guest

Then you'll need either a custom function or a script, as this is a recursive operation*. What is the purpose here - why don't you split these into records and fields, as it seems they should properly be?

Is the minus an indication of negative number, or something else?

---

(*) or a very long formula that repeats everything 7 times.

This field is "For Display Purposes Only"

Our "Live" billing system is an old DOS like screen.

I am importing a data dump from that system, so it can be viewed in a more user-friendly way.

The minus does indicate a negative number

Edited September 5, 200817 yr by Guest

I don't know how "friendly" such display is, but you could try something relatively simple like:

Let ( [

v = Substitute ( ImportedText ; "*" ; ¶ )

] ;

GetValue ( v ; 1 ) & Let ( b = GetValue ( v ; 2 ) ; Case ( b < 0 ; ": -$" ; ": $" ) & Div ( Abs ( b ) ; 100 ) & "." & Right ( b ; 2 ) )



& Case ( GetValue ( v ; 3 ) ; 

" | " & GetValue ( v ; 3 ) & Let ( b = GetValue ( v ; 4 ) ; Case ( b < 0 ; ": -$" ; ": $" ) & Div ( Abs ( b ) ; 100 ) & "." & Right ( b ; 2 ) )

)



& Case ( GetValue ( v ; 5 ) ; 

" | " & GetValue ( v ; 5 ) & Let ( b = GetValue ( v ; 6 ) ; Case ( b < 0 ; ": -$" ; ": $" ) & Div ( Abs ( b ) ; 100 ) & "." & Right ( b ; 2 ) ) 

)



)

This will handle up to 3 pairs, and you can expand it easily by duplicating the last part and raising the value numbers.

Thanks Comment