Jump to content
Sign in to follow this  
Aussie John

filter a field

Recommended Posts

Hi - I need to delete the text part of a field eg

"Y2.20.00 room name" to become

"Y2.20.00" Is this possible?

To complicate things the order might change and the number of words vary. eg

"another room name Y3.30"

Sometimes either the name or number is inside brackets

cheers

Edited by Guest

Share this post


Link to post
Share on other sites

It's probably possible - but we'd need a better formulation of the rules: what to keep and what to discard. It's too easy to jump to the wrong conclusions when all you have is an example or two.

Share this post


Link to post
Share on other sites

Hi Comment

The portion i want to extract is always a letter (or two) followed by numbers and separated by periods

eg

Y2.3.04 or

hd1.4.03.05

x.1.2.4

the discarded part is always text and there is always at least a space separating the parts.

some additional examples

hf2.001 (CP Atrium)

lb2.207 (LT14)

LT 1 (I2.02)

Edited by Guest

Share this post


Link to post
Share on other sites

OMG, what a nightmare. Hm... try:


Let ( [

mask = Substitute ( text ; [ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ) ;

mark = Position ( mask ; "#." ; 1 ; 1 ) ; 

start = Position ( " " & text ; " " ; mark ; - 1 ) ; 

cut = Right ( text ; Length ( text ) - start + 1 )

] ;

LeftWords ( cut ; 1 )

)

Share this post


Link to post
Share on other sites

Indeed is a nightmare

Unfortunately still some errant fields

UA (EAST DINING ROOM - jb1.103)

LT13 (jb1.103)

J.1.23

Maybe not possible?

Share this post


Link to post
Share on other sites

UA (EAST DINING ROOM - jb1.103)

LT13 (jb1.103)

What is your expected result here? And what do you get?

J.1.23

Oh, I see - the period can come between letters and digits, too. OK, so how about:


Let ( [

mask = " " & Substitute ( text ; [ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ) & " " ;

mark = Position ( mask ; "#." ; 1 ; 1 ) ; 

start = Position ( mask ; " " ; mark ; - 1 ) ; 

end = Position ( mask ; " " ; mark ; 1 ) 

] ;

Substitute ( Middle ( text ; start ; end - start ) ; [ "(" ; "" ] ; [ ")" ; "" ] )

)

Share this post


Link to post
Share on other sites

This seems to work

let([

mark = Position (text ; "." ; 1 ; 1 ) ;

mark2 =Position (text ; " " ; 1 ; 1 )] ;

Case(mark

except for J.1.23 (which reads as 2 words). I guess I can to a test on the result - case length(text)=1 then i use leftwords(text,2)

Share this post


Link to post
Share on other sites

mark = Position (text ; "." ; 1 ; 1 ) ;

Are you saying there won't be periods anywhere else in the text?

I guess I can to a test on the result - case length(text)=1 then i use leftwords(text,2)

I don't think that will work for something like "AB.123".

Share this post


Link to post
Share on other sites

Thanks for your time Comment,

Are you saying there won't be periods anywhere else in the text?

I don't think that will work for something like "AB.123".

Yes the section to be removed contains text only (sorry if not clear in the OP)

my final solution

code

LW=LeftWords(text;1);

RW=RightWords(text;1);

mark = Position ( text ; "." ; 1 ; 1 ) ; 

mark2 =Position (text ; " " ; 1 ; 1 ) ; 

text=Case(mark

LW=LeftWords(text;1);

RW=RightWords(text;1)

];

Case(mark

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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