Get only the first possible Number

[psk180590]

Hello All,

I have data in Text format which is not consistent. Few examples below:

• 5/6
• 10 packets
• More than the standard, 12 packets available
• 5*3 packets
• 5packets

The data looks very comical, but i have to some way take out only the first possible digit.

From the above example data

• 5/6 - 5
• 10 packets - 10
• More than the standard, 12 packets available - 12
• 5*3 packets - 5
• 5packets - 5

The numbers in bold is my expected result.

I'm not sure if this can really be achieved. Please throw in some suggestions or if any previously faced encounters.

Thanks in Advance!!

Sai.

[Anil_Babu_Samineni]

From 5/6 and 5*3 ?? Why only 5 and why not 6 and 3??

[psk180590]

Good question!

It says

5/6 means 5 packets out of 6 are in order

5*3 means 3 packets should have been present but, instead we have 5.

[sunny_talwar]

Try this

Table:

LOAD *,

FindOneOf(Value, '0123456789') as Start,

FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')), 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;')-1 as End,

Mid(Value, FindOneOf(Value, '0123456789'), FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')), 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;')-1) as Req;

LOAD * INLINE [

    Value

    5/6

    10 packets

    "More than the standard, 12 packets available"

    5*3 packets

    5packets

];

[sunny_talwar]

For the sake of clarity, you can see this also

Table:

LOAD *,

Mid(Value, Start, End) as Req;

LOAD *,

FindOneOf(Value, '0123456789') as Start,

FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')), 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;')-1 as End;

LOAD * INLINE [

    Value

    5/6

    10 packets

    "More than the standard, 12 packets available"

    5*3 packets

    5packets

];

[trdandamudi]

May be as below on the chart (Assuming there will be only * and / in the data):

[psk180590]

stalwar1‌

Thanks for the suggestion, it works perfectly except for some cases

Total 3 - Gives result as Null where as the answer should be 3

2Packets 100gm - Gives result as 2P where as the answer should be only 2

Also when there is empty space after the Text example 4packets    (followed by space) - Gives result as 4p where as the answer should be 4.

Regards

Sai.

[sunny_talwar · Accepted solution]

Not sure what is wrong with 4 packets, but rest of them work now

Table:

LOAD *,

Mid(Value, Start, End) as Req;

LOAD *,

FindOneOf(Value, '0123456789') as Start,

FindOneOf(Mid(Value, FindOneOf(Value, '0123456789')+1) & '/', 'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+}{":?><~`-= /\][.,;ABCDEFGHIJKLMNOPQRSTUVWXYZ') as End;

LOAD * INLINE [

    Value

    5/6

    10 packets

    "More than the standard, 12 packets available"

    5*3 packets

    5packets

    Total 3

    2Packets 100gm

    4packets 

];

[antoniotiman]

Try this

LOAD Value,Left(Field,FindOneOf(Field,'*/@')-1) as Field;
LOAD Value,Replace(LTrim(KeepChar(Value,'0123456789*/ ')),' ','@')&'@' as Field INLINE [
Value
5/6
10 packets
"More than the standard, 12 packets available"
5*3 packets
5packets
Total 3
2Packets 100gm
4p];

[MarcoWedel]

Hi,

another solution might be:

mapNonNumToSpace:
Mapping LOAD Chr(RecNo()), ' ' AutoGenerate 65535 Where not IsNum(Chr(RecNo()));

table1:
LOAD Text(Text) as Text,
    SubField(Trim(MapSubString('mapNonNumToSpace',Text)),' ',1) as Number
INLINE [
    Text
    No number here
    1
    23
    345
    1a2b3c
    5/6
    10 packets
    "More than the standard, 12 packets available"
    5*3 packets
    5packets
    Total 3
    2Packets 100gm
    "4packets    "
    "    6packets"
];

hope this helps

regards

Marco