Advanced text trimming

[Matthew Wood]

I have a field that is has data in it like this:

AMBMasaiHan_NHF0032.L.aif

I would like to be able to trim out everything in that field before the "_" and after the first "." from the left, leaving "NHF0032" as the only data remaining in the field. The variables are that the number of characters before the "_" and after "." are never constant. Is there a way to do this using the Specify Calculations in scripting? Some sort of advanced trim?

Thank you,

Matthew Wood

[Raybaudi]

Hi

Middle( field ; Position( field ; "_" ; 1 ; 1 ) + 1 ; Position( field ; "." ; 1 ; 1 ) - Position( field ; "_" ; 1 ; 1 ) - 1 )

[Matthew Wood]

That worked great except I had to change all the ";" to ",".

Thank you so much!!

Matthew

[Raybaudi]

Hi Matthew

yes, I forgot to change those ";" to ","...international version 6 use those symbols vs american version.

[Tony Martin]

Hi

Middle( field ; Position( field ; "_" ; 1 ; 1 ) + 1 ; Position( field ; "." ; 1 ; 1 ) - Position( field ; "_" ; 1 ; 1 ) - 1 )

Hi again! Can someone please explain in plain english why this works? I'm trying real hard to "get" these parsing concepts but they just aren't registering.

I ask because I have a similar field (and thank god it's the last one) - it's a two-line field and the data within looks like this:

5576 W ROCHELLE AVE UT 43D

LAS VEGAS

(there's a ¶ at the end of the top line)...so "technically" it's:

5576 W ROCHELLE AVE UT 43D¶LAS VEGAS

...There's other records in that field which looks like these:

7916 BLUE BROOK DR¶LAS VEGAS

2325 WINDMILL PKWY #721¶HENDERSON

Now, here's what I need: In three separate calculations, I need to achieve three separate results:

Calc #1) A result of the street number, direction, name amd type.

Calc #2) A result of everything between " UT " and the ¶ symbol, OR everything between " #" and the ¶ symbol.

Calc #3) A result of everything after the ¶ symbol.

If you could help me with the calc's, that would be great, but in order for me to "get it" (and not have to come running back here all the time [not that I don't love you guys - I do]!), might someone please explain the architecture (sp) involved - the "why-it-works" part? I know I'm smart enough for this...buuuut, this is what happens when you mix work with listening to 2 year olds scream and tons of caffiene. :

Thank you, thank you! I finally got some sleep last nite because of YOU fine people at FMFORUMS!!

Tony In Vegas

P.S. - Still using yucky 5.5v1!

[Raybaudi]

Hi

Calc #1)

Case(

PatternCount ( field , " UT " ) , Left ( field , Position ( field , " UT " , 1 ; 1) - 1 ) ,

PatternCount ( field , " #" ) , Left ( field , Position ( field , " #" , 1 , 1) - 1 ) ,

Left ( field , Position ( field , "¶" , 1 , 1) - 1 )

)

Calc #2)

Case(

PatternCount ( field , " UT " ) , Middle ( field , Position ( field , " UT ", 1 , 1 ) + 4 , Position ( field , "¶" , 1 , 1 ) - Position ( field , " UT " , 1 , 1 ) - 4 ) ,

PatternCount ( field , " #" ) , Middle ( field , Position ( field , " #" , 1 , 1 ) + 2 ; Position ( field , "¶" , 1 , 1 ) - Position ( field , " #" , 1 , 1 ) - 2 ) ,

""

)

Calc #3)

Middle ( field , Position ( field , "¶" , 1 , 1 ) + 1 , Lenght ( field ) - Position ( field , "¶" , 1 , 1 ) )

[paste120]

Hi i tryed to adapt what you did with first post to my situation but am having a little trouble.

I have data like this in fields:

http://dodad:[email protected]/index.php

or

http://egg:[email protected]/sub/index.php

(there not fixed sizes)

and I was wondering how I can shorten them to just be:

mywebsite.com

That value would then go into another table to make it easier to search, but i can do that. I just cant figure out how to get rid of the exess data.

Edited December 1, 2006 by Guest

[comment]

Try something like:

Let ( [

start = Position ( yourfield ; "@" ; 1 ; 1 ) + 1 ;

end = Position ( yourfield ; "/" ; start ; 1 )

] ;

Middle ( yourfield ; start ; end - start )

)