INTERACT FORUM

Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1]   Go Down

Author Topic: Expression to get last name from field  (Read 1965 times)

llafriel

  • World Citizen
  • ***
  • Posts: 230
Expression to get last name from field
« on: March 17, 2009, 08:25:55 am »

Is there a way to get all characters from right to the first open space? A sort of 'Right(value,character)', where value is the string and character is the letter where to stop.

I'd like to do Right([Composer]," "), to get the last name of the composer.
Logged

Lasse_Lus

  • Citizen of the Universe
  • *****
  • Posts: 999
Re: Expression to get last name from field
« Reply #1 on: March 17, 2009, 08:53:33 am »

with function ListItem you can do that, but it needs be a "List field" you can create a testfield, copy Composer to that field replace "space" with "semicolon"

and use the function ListItem([TESTComposer],1)

with the function Right you count and it works if you have the same number of letters all the time, but i doubt that you have
Logged
MT5FR

llafriel

  • World Citizen
  • ***
  • Posts: 230
Re: Expression to get last name from field
« Reply #2 on: March 17, 2009, 11:12:38 am »

with the function Right you count and it works if you have the same number of letters all the time, but i doubt that you have

Wich is why I wanted an expression that looks for the first space.
Your solution works also, but neither will work when there are more than two names.. Guess I'll have to do it manually.
Logged

Lasse_Lus

  • Citizen of the Universe
  • *****
  • Posts: 999
Re: Expression to get last name from field
« Reply #3 on: March 17, 2009, 11:18:59 am »

maybe you can involve the  ListCount(...) function and if function
Logged
MT5FR

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: Expression to get last name from field
« Reply #4 on: March 17, 2009, 11:54:13 am »

listitem can be run on any field, not just list type fields.

for the composer field, listitem( [composer],1,/ ) returns the first item from the composer field, using the space character as the specified delimiter.

To get the last item, we need to know how many items a given string has, which is not so straight forward.
I tried nesting a listcount() function within a listitem function, but this returned a syntax error :(

This workaround seems to work:

1. Create a new field. Give it a short name, let's say, Item. The field will be "calculated data" and the expression will be: listcount([composer],/ )
2. Enter the following expression to retrieve the last item from a space delimited composer field: listitem( [composer],[item],/ )

-marko.

llafriel

  • World Citizen
  • ***
  • Posts: 230
Re: Expression to get last name from field
« Reply #5 on: March 17, 2009, 01:21:03 pm »

Thanks to both of you! Works well enough.
Logged

hit_ny

  • Citizen of the Universe
  • *****
  • Posts: 3310
  • nothing more to say...
Re: Expression to get last name from field
« Reply #6 on: March 17, 2009, 02:42:55 pm »

I tried nesting a listcount() function within a listitem function, but this returned a syntax error :(

What about ?

listitem([composer],[=listcount([composer],/ )],/ )
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: Expression to get last name from field
« Reply #7 on: March 17, 2009, 02:55:48 pm »

nope, but you were on the right track...

=listitem( [composer],listcount([composer],/ ),/ )

works, without the need for any additional custom fields. Nice one hit_ny :)

hit_ny

  • Citizen of the Universe
  • *****
  • Posts: 3310
  • nothing more to say...
Re: Expression to get last name from field
« Reply #8 on: March 17, 2009, 03:10:15 pm »

Cool  :D

but still don't understand the use of '/' to imply delimiter is " "  ?
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: Expression to get last name from field
« Reply #9 on: March 17, 2009, 03:22:05 pm »

Space characters after commas are ignored by the expression evaluator. Placing the forward slash before the space 'escapes' the space character and so the expression evaluator treats it as a part of the expression.

-marko.
Pages: [1]   Go Up