isolating a number from text with calculation

[vierdewereld]

Once again I need your help with a simple thingy. It's 8:50 p.m. and I still haven't solved this simple calculation. I know some of you have this done in 2 minutes so could you plz help me out, while I finally roll into bed.

I would like to have a calculationfield that presents the numbers 375 from the field Street, which contains: John S. Christianstreet 375-22

Easy huh, It would be nice it that calculation also would work when the field Street contains: Dr Docmanstreet 19 - 3 or 19 -3

Many thanks,

Rakesh

[djgogi]

There was basically the same question few weeks ago.

Here's the suggestion I gave.

TextToNum(Case(Length(TextToNum(text)),LeftWords(Right(

text,

Length(text)-Min(

Case(Position(text, "1", 1, 1),Position(text, "1", 1, 1),64000),

Case(Position(text, "2", 1, 1),Position(text, "2", 1, 1),64000),

Case(Position(text, "3", 1, 1),Position(text, "3", 1, 1),64000),

Case(Position(text, "4", 1, 1),Position(text, "4", 1, 1),64000),

Case(Position(text, "5", 1, 1),Position(text, "5", 1, 1),64000),

Case(Position(text, "6", 1, 1),Position(text, "6", 1, 1),64000),

Case(Position(text, "7", 1, 1),Position(text, "7", 1, 1),64000),

Case(Position(text, "8", 1, 1),Position(text, "8", 1, 1),64000),

Case(Position(text, "9", 1, 1),Position(text, "9", 1, 1),64000)

)+1

), 1)))

Dj

[Lee Smith]

Hi DJ,

Nice calculation and I think I follow most of what it is doing, except I don't know what the 64000 at the end of each line is doing?

TIA

Lee

[geod]

Hi Lee, I believe the numbre is the maximu number of character allowed in a text field.

Graham

[Lee Smith]

Well, that was my guess too. But I wanted to verify it.

Thanks for you replay.

Lee

[djgogi]

Yes 64000 stands for max num of chars in text field.

It is the shortcut to use with "Right" text functions, without checking how many characters (or words) are really available.

Dj

BTW for decimal numbers (with or without the leading zero) the calc will be slightly more complex

TextToNum(Case(Length(TextToNum(text)),LeftWords(Right(

text,

Length(text)-Min(

Case(Position(text, "1", 1, 1),Position(text, "1", 1, 1),64000),

Case(Position(text, "2", 1, 1),Position(text, "2", 1, 1),64000),

Case(Position(text, "3", 1, 1),Position(text, "3", 1, 1),64000),

Case(Position(text, "4", 1, 1),Position(text, "4", 1, 1),64000),

Case(Position(text, "5", 1, 1),Position(text, "5", 1, 1),64000),

Case(Position(text, "6", 1, 1),Position(text, "6", 1, 1),64000),

Case(Position(text, "7", 1, 1),Position(text, "7", 1, 1),64000),

Case(Position(text, "8", 1, 1),Position(text, "8", 1, 1),64000),

Case(Position(text, "9", 1, 1),Position(text, "9", 1, 1),64000),

Case(Position(text, "0", 1, 1),Position(text, "0", 1, 1),64000)

)+2

), 1)))

[Lee Smith]

In a word - WOW

Thank you for a second calculation and the explaination.

Lee

[vierdewereld]

Super User...

My apologies that the question was asked before. Back then I was to tired to look thoroughly.

Thanks for your solution.

Many thanks djogogi,

I noticed a problem when a streetname like these came up:

[color:"red"]2nd troebadoerstreet 154 - 7 gives 2 insead of 154

Is there a way to keep the starting 0?

[color:"blue"] Whereitisland 020 want it to show 020

Many thanks for your time again.

[Vaughan]

Numbers don't have leading zeros.

If you want to have a leading zero you need to convert the number to text again and add the zero.