Jump to content

Condense several fields


LaRetta
 Share

This topic is 5697 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. :crazy2:

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 by Guest
Corrected first calc
Link to comment
Share on other sites

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

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

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

)

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)? :wink2:

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

LaRetta

Edited by Guest
Added Update
Link to comment
Share on other sites

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'.

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 5697 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.