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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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