Condense several fields

[LaRetta]

I recently provided this calculation which removes blank lines in a list of phone numbers:

Substitute ( Trim[color:green]All ( Ph1 & " " & Ph2 & " " & Ph3 [color:green]; 0 ; 0 ; " " ; ¶ )

This works for phone numbers because there are no spaces in the example provided. But if the numbers are (515) 342-1135, it will break. I wanted an alternative in case there were spaces (which would accept multiple-word lines) so it could be used with addresses etc. It kinda looks confusing but this would work:

Substitute ( TrimAll ( Substitute ( Ph1 & "~" & Ph2 & "~" & Ph3 ; [ " " ; "/" ] ; [ "~" ; " " ] ) ; 1 ; 1 ) ; [ " " ; ¶ ] ; [ "/" ; " " ] )

It is possible the new List() function in 8.5 makes this all unnecessary; I wouldn't know yet. But for all of us still pre-8.5, eliminating the IsEmpty() tests around each field would make it easier to work with; since it seems I'm always needing to do this. Awhile back, Comment presented a technique using Substitute() which I named the Tower of Hanoi calc because of its similarity to a mathematical puzzle (pointed out to me by Soren). The Substitute() calc switched characters until the result was achieved. I can't find it now but that's where I am trying to go.

Bob Weaver and Comment have also produced calcs which remove (unlimited) multiple carriage returns and they have used THAT to strip extra lines. But using TrimAll() might work without additional evals (by its nature, it removes all excess) if I can make it understandable and flexible. I would also like to turn it into a custom function so it can be defined as:

Condense ( [ field1 ; field2 ; field3 ] ; separator ; delimiter ) so I could specify whether to end up with a multiline list, a comma-separated list and so forth. And I would like to define the word separator as well, since / can be dangerous in some text or text-date strings. But I can't figure out how to make a custom function accept a list (square brackets). I'm not putting this request in Custom Functions because it won't NEED to be a custom function (I don't believe). And maybe this idea won't be more efficient than stripping out extra carriage returns ...

Ideas? Alternate approaches?

LaRetta :wink2:

Edited February 20, 2007 by Guest
Corrected first calc

[Genx]

Condense ( field1 & "|" & field2 & "|" & field3 & "|" & field4 ; separator?? ; delimiter )

Let( fieldList = Substitute( fieldList ; "|" ; "~" ) ;

Substitute ( TrimAll ( Substitute ( fieldList ; [ " " ; "/" ] ; [ "~" ; " " ] ) ; 1 ; 1 ) ; [ " " ; delimiter ] ; [ "/" ; " " ] )

)

[Genx]

... or just use the tidal character straight away, but the pipe seems to be a sort of standard for delimiting lists in FM where the pilcrow can't be.

[LaRetta]

Hi Genx! Thanks for responding!

Yep, I used / to signify that it might be dangerous and needs to be specified within each calculation instead of hard-coded within the custom function itself. I've been looking at special characters and trying to determine which will work on both Mac and Pc. Basically, you are taking my calc and just rearranging it but it is still not formatted in List format (square brackets) in the CF. Maybe square brackets can't be used within a CF? Anyway, I probably wasn't clear in that the tilde (which you cutely called the tidal) is the FIELD delimiter (in this case line) but I also want to specify the word separator (which will only be used temporarily within the calc).

I see possibility of breaks which I'll want to account for. Realistically, none of the fields will have carriage returns in them already (but they might). Otherwise, I think it'll be pretty safe (except for things such as leading/trailing space within a field but I always keep free-form text fields trimmed anyway. I know we can't create the perfect account-for-everything calc here but that's okay.

How can I make a List in a custom function like Let() and Substitute() uses? And can this approach be more elegant? I'll keep playing with this idea. :smile2:

[Genx]

Nah I just checked , no way to use square brackets as far as i can tell.

And, well i can't help it, it looks tidal... ~ i mean look at it, tide goes out, tide goes in, tide goes out, tide goes in.

[comment]

Did you mean this one?

[LaRetta]

Hi Michael!

I think the one that actually inspired me to plant character switcheroo in my mind was one you posted on Cafe' right before this one (right after I learned about Tower of Hanoi) but yes! My calc (above) wouldn't protect from leading/trailing spaces in the fields and you've covered that as well.

And of course I don't want to hard-code the fields in a custom function but I won't need to because I don't have to include that § within my calculation - I can use ¶ between the fields (which makes sense for a list) AND as the separator (if I wish) and it works just as well!

I had enjoyed learning 1) the power of character switcheroo and 2) how using List within Substitute() made it MUCH easier to use. But I had missed the POWER that you had used TrimAll() to remove unlimited numbers of a character by playing the switcheroo game. I still want to make a CF out of it, if that's okay with you. It will come in handy for many things. But I can't call it Condense() ... for some reason, FM says that it is an invalid name (function, parameter or keyword)?

UPDATE: Ah. TextStyleAdd() uses Condense ...

LaRetta

Edited February 19, 2007 by Guest
Added Update

[comment]

I am not sure I understand your exact requirements. Hiding the spaces and applying TrimAll() to another character works for ANY character. If you only need to eliminate multiple consecutive ¶s, then

List ( field1 ; field2 ; ... fieldN )

will do it - both between and within fields.

P.S. I would call this process as 'decimating a character run'.

[LaRetta]

No list function in vs. 8 and it needs to be flexible as well; not just carriage returns. It is the two-fold technique that I enjoyed learning - decimating a character run using TrimAll() and using Substitute() to respecify the characters (Tower of Hanoi). :wink2:

[LaRetta]

I just noticed an error in the original calc I provided for a sample of a prior post. I've created it (in green in my original thread). :(