Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to take return all the digits before the FIRST decimal in a list.
I have
1920.1008.678
1800.786.894
So should return :
1920
1800
Thanks
Hi,
You can try mid function as below.
=Mid('1920.1008.678',1,Index('1920.1008.678','.',1)-1)
Regards,
Kaushik Solanki
Or may be this:
SubField(FieldName, '.', 1)
I have a long list of such numbers not just the two examples mentioned. Also I have some with just one decimal such as
1820.898
Hi,
The Number is just an example, you will have field name instead of the value in your script.
So your expression will be
Load *,Mid(FieldName,1,Index(FieldName,'.',1)-1) as Number From xyx;
Or as Sunny said it will be
Load *,Subfield(FieldName,'.',1) as Number From xyx;
Regards,
Kaushik Solanki
Thanks both.
Sunny I tried your solution and whilst it does work, if there are two separate rows with the same value it only returns for one of them.
So if I have
1880.889.190
1880.889.190
it only returns 1880 for the first row and blank for the second
It must be returning both values, but just not showing it. Add a new field called RowNo() as Key and use that in your table box to see both the rows individually.
Try simply this:
left(Value, 4)
Should surfice you
Regrads,
MB
just for fun another one:
=TextBetween('\'&YourField,'\','.')