filter a field

[Aussie John]

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 May 26, 2009 by Guest

[comment]

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.

[Aussie John]

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 May 26, 2009 by Guest

[comment]

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 )

)

[Aussie John]

Indeed is a nightmare

Unfortunately still some errant fields

UA (EAST DINING ROOM - jb1.103)

LT13 (jb1.103)

J.1.23

Maybe not possible?

[comment]

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 ) ; [ "(" ; "" ] ; [ ")" ; "" ] )

)

[Aussie John]

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)

[comment]

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".

[Aussie John]

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