I have a (predominantly search-oriented) Full Name field consisting of a
calculation:
First Name & " " & Middle Name & " " & Last Name

How do I avoid the space related to the Middle Name, when the field is
empty? Haven't been able to find the right function for that, though
it's probably quite simple :-) I thought of filtering, but that allows
characters and can't seem do disallow double spaces... Substitute should
be able to do that but get it to work together with the rest of the
calculation.

Thx in advance!

:-) Esben

Mail: nameasofabove_h [at] get2net.dk

On Sep 8, 2:43 pm, e... [at] nospamget2net.dk (Esben) wrote:
> I have a (predominantly search-oriented) Full Name field consisting of a
> calculation:
> First Name & " " & Middle Name & " " & Last Name
>
> How do I avoid the space related to the Middle Name, when the field is
> empty? Haven't been able to find the right function for that, though
> it's probably quite simple :-) I thought of filtering, but that allows
> characters and can't seem do disallow double spaces... Substitute should
> be able to do that but get it to work together with the rest of the
> calculation.
>
> Thx in advance!
>
> :-) Esben
>
> Mail: nameasofabov... [at] get2net.dk

First Name & Case(not isEmpty(Middle Name); " " & Middle Name) & " "
& Last Name

Grip <grip [at] cybermesa.com> wrote:

> On Sep 8, 2:43 pm, e... [at] nospamget2net.dk (Esben) wrote:
> > I have a (predominantly search-oriented) Full Name field consisting of a
> > calculation:
> > First Name & " " & Middle Name & " " & Last Name
> >
> > How do I avoid the space related to the Middle Name, when the field is
> > empty? Haven't been able to find the right function for that, though
> > it's probably quite simple :-) I thought of filtering, but that allows
> > characters and can't seem do disallow double spaces... Substitute should
> > be able to do that but get it to work together with the rest of the
> > calculation.
> >
> > Thx in advance!
> >
> > :-) Esben
> >
> > Mail: nameasofabov... [at] get2net.dk
>
> First Name & Case(not isEmpty(Middle Name); " " & Middle Name) & " "
> & Last Name

Great! - thanks - that worked!

I actually wondered if isEmpty could be put in the negative...!

Esben

In article <1i45bqz.dhfnbggju07gN%esha [at] nospamget2net.dk>,
esha [at] nospamget2net.dk (Esben) wrote:

> Grip <grip [at] cybermesa.com> wrote:
>
> > On Sep 8, 2:43 pm, e... [at] nospamget2net.dk (Esben) wrote:
> > > I have a (predominantly search-oriented) Full Name field consisting of a
> > > calculation:
> > > First Name & " " & Middle Name & " " & Last Name
> > >
> > > How do I avoid the space related to the Middle Name, when the field is
> > > empty? Haven't been able to find the right function for that, though
> > > it's probably quite simple :-) I thought of filtering, but that allows
> > > characters and can't seem do disallow double spaces... Substitute should
> > > be able to do that but get it to work together with the rest of the
> > > calculation.
> > >
> > > Thx in advance!
> > >
> > > :-) Esben
> > >
> > > Mail: nameasofabov... [at] get2net.dk
> >
> > First Name & Case(not isEmpty(Middle Name); " " & Middle Name) & " "
> > & Last Name
>
> Great! - thanks - that worked!
>
> I actually wondered if isEmpty could be put in the negative...!

Instead of the negative, you can simply turn the results around
(although Grip's calculation is missing the "otherwise" result since
it's not really needed).
ie.
First Name & Case(IsEmpty(Middle Name); ""; " " & Middle Name)
& " " & Last Name

If the Middle Name is empty, add 'nothing' (ie. "" no space in between).


There are of course various other ways too. You could use the Trim
function which removes exces spaces from the start and end of text.
eg.
Trim(First Name & " " & Middle Name) & " " & Last Name

In fact, due to "human error" in doing data entry, it may even be a
good ide to trim all the fields as you add them too.
eg.
Trim(Trim(First Name) & " " & Trim(Middle Name)) & " "
& Trim(Last Name)

You may also need to remove carriage return characters using the
Substitute function.
eg.
Substitute(
Trim(Trim(First Name) & " " & Trim(Middle Name)) & " "
& Trim(Last Name);
*P;
"")

where *P is the "backwards P" symbol for the return character which
appears on one of the buttons in the Define Calculation window.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Helpful Harry <helpful_harry [at] nom.de.plume.com> wrote:

> In article <1i45bqz.dhfnbggju07gN%esha [at] nospamget2net.dk>,
> esha [at] nospamget2net.dk (Esben) wrote:
>
> > Grip <grip [at] cybermesa.com> wrote:
> >
> > > On Sep 8, 2:43 pm, e... [at] nospamget2net.dk (Esben) wrote:
> > > > I have a (predominantly search-oriented) Full Name field consisting of a
> > > > calculation:
> > > > First Name & " " & Middle Name & " " & Last Name
> > > >
> > > > How do I avoid the space related to the Middle Name, when the field is
> > > > empty? Haven't been able to find the right function for that, though
> > > > it's probably quite simple :-) I thought of filtering, but that allows
> > > > characters and can't seem do disallow double spaces... Substitute should
> > > > be able to do that but get it to work together with the rest of the
> > > > calculation.

> > >
> > > First Name & Case(not isEmpty(Middle Name); " " & Middle Name) & " "
> > > & Last Name
> >
> > Great! - thanks - that worked!
> >
> > I actually wondered if isEmpty could be put in the negative...!
>
> Instead of the negative, you can simply turn the results around
> (although Grip's calculation is missing the "otherwise" result since
> it's not really needed).
> ie.
> First Name & Case(IsEmpty(Middle Name); ""; " " & Middle Name)
> & " " & Last Name
>
> If the Middle Name is empty, add 'nothing' (ie. "" no space in between).
>
>
> There are of course various other ways too. You could use the Trim
> function which removes exces spaces from the start and end of text.
> eg.
> Trim(First Name & " " & Middle Name) & " " & Last Name
>
> In fact, due to "human error" in doing data entry, it may even be a
> good ide to trim all the fields as you add them too.
> eg.
> Trim(Trim(First Name) & " " & Trim(Middle Name)) & " "
> & Trim(Last Name)

I suppose it'd be better to trim the original fields - i need to trim
final spaces but not inbetween spaces....
>
> You may also need to remove carriage return characters using the
> Substitute function.
> eg.
> Substitute(
> Trim(Trim(First Name) & " " & Trim(Middle Name)) & " "
> & Trim(Last Name);
> *P;
> "")
>
> where *P is the "backwards P" symbol for the return character which
> appears on one of the buttons in the Define Calculation window.

Couldn't that be solved by filtering allowed characters instead!?

Esben