Combining name with spaces

Questions
1

I have calculation fields that combine peoples names. I have first, last, middle, suffix, and company. I have been making evolving calculations to combine them and it seems every evolution brings up another issue. I either add a space where I do not want one or not add a space at all.

Below is the current attempt and this is not taking into account that there might not be a middle name and hence dose not add a space between first and last name. I am sure if I keep evolving this code I will figure out something that will work but I figured it would be smarter to ask for help as I am sure someone has solved this issue already.

Let 
  ( 
[ 
   First                         = Trim ( Parcel Info | Current Owner | First Name 1 ) 
 ; Middle                        = Trim ( Parcel Info | Current Owner | Middle Initial 1 )
 ; Last                          = Trim ( Parcel Info | Current Owner | Last Name 1 )
 ; Suffix                        = Trim ( Parcel Info | Current Owner | Suffix 1 )
 ; Company                       = Trim ( Parcel Info | Current Owner | Company )


 ] ; 


Case ( not IsEmpty ( Last ) ; 
  Last
 )
& 
Case ( not IsEmpty ( Middle) ;
 Case ( not IsEmpty ( Last ) ; 
       " " & Middle; 
      Middle
      )
  )
& 
Case ( not IsEmpty ( First) ;
 Case ( not IsEmpty ( Middle) ; 
       " " & First; 
      First
      )
  )
& 
Case ( not IsEmpty ( Suffix  ) ;
 Case ( not IsEmpty ( First) ; 
       " " & Suffix  ; 
      Suffix  
      )
  )

& 
Case ( not IsEmpty ( Company  ) ;
 Case ( not IsEmpty ( Suffix ) ; 
       " " & Company  ; 
      Company  
      )
)
)
1 Like
2

Could you include some examples of what went wrong?

It may be easier to post-process what you end up with rather than trying to get this one CC perfect.

3

One example below

image

Using the calculation above this ends up not adding a space between first and last name so it ends up being HenryRobinson. I would like it to be Henry Robinson. But in my experience people seem to enter all kinda of odd combination of names. They might enter middle and last but for some odd reason skip the first name. I need a calculation to account for all possible ways a user could enter the name.

4

Seems to me the following would be much simpler:

substitute (
	First & " " & Middle & " " & Last & " " & Suffix & " " & Company;
	["    "; " "];
	["   "; " "];
	["  "; " "];
	[" "; " "]
)

Hope this helps.

2 Likes
5

Man it is amazing how many options one has when building code. Some times the solution is so simple you just do not see it. Thanks for much for this it worked perfect. Well almost I had to add a space to the last substitute to 2 spaces instead of one.

I am also most sad though as I was about to try my first While in a calculation. I am sure I will have another chance soon though.

Thanks again.

1 Like
6

What about following:

Substitute ( List (
First
; Middle
; Last
; Suffix
; Company
) ; ¶ ; " " )

best
Otmar

10 Likes
7

That's cool.

For a more general solution, I might consider using MBS or BE with a regular expression that would handle any number of spaces.

Search Pattern: \s{2,}
Replace pattern: a single space

8

Thanks @Otmar, I was wondering how long it would take for this simple solution to be offered.

9

This dose look like a cool option and as long as in the above list you still use trim() it would take care of human entered extra spaces.

10

You could consider using Trim(self) as an auto-enter calc on those fields.

11

Hi @Malcolm , went through all those workarounds with case and replaces too before I came to this.
The ignoring of empty values of the list function can be very handy, although can be a pitfall too :wink:

1 Like
12 
LeftWords ( First & " " & Middle & " " & Last & " " & Suffix & " " & Company ; 100 )

Edit: Aah – I’ve posted first and then tried. Unfortunately my idea doesn’t eleminate spaces if fields are empty.

13

The ignoring of empty values is such a great feature when you are stacking a list of optional values - which happens a lot with addresses.

1 Like
14

It would also have issues on names that have more than one word. Would run into this a lot for companies.

15

No, that would in my opinion not be a problem. Only the double spaces if a field is empty. You only have to take a value for the count of words higher than the max number of concatenated words.

16

Substitute ( LeftWords ( First & " " & Middle & " " & Last & " " & Suffix & " " & Company ; 100 ) ; " " ; " " ) // replace two spaces by one

17

Interesting idea. I think I will need to try it to learn more. Not sure I have used LeftWords(). Would this take into account more than 2 spaces? Or better yet any multiple of spaces?

18 
Let (
   ~text = First & " " & Middle & " " & Last & " " & Suffix & " " & Company ;
   //calculation
   Subtitute ( LeftWords ( ~text ; WordCount ( ~text ) ) ; "  " ; " " )

No guess anymore how many words could exist.

19

I'd probably lean towards what @bdbd posted.

If I needed to reduce arbitrary quantities of space chars, I'd reach for something trickier, but I'd still probably keep it within the FMP calc engine functions.

Also:

I've seen the use of the TrimAll function to address this type of scenario, but I don't tend to use it, just because I've never completely understood what the docs are trying to tell me for that function.

I believe the usage might go something like this;

TrimAll( First & " " & Middle & " " & Last & " " & Suffix ; 0 ; 2 )

4 Likes
20

@steve_ssh Short and elegant!