Trim with if

[#1]

Hi I have a field like so:

test 001

test 002

test 010

test 013

test 234

I need to create a new column but format the values so I am only left with the whole numbers with no proceeding zero's:

1

2

10

13

234

I'm assuming I use the trim function with an if statement but just struggling to get it to work. Any idea's?

Thanks

[maxgro]

num(SubField(yourfield, ' ', 2))

[sunny_talwar]

You can also do

SubField(Fieldname, ' ', 2) * 1

[MarcoWedel · Accepted solution]

Hi,

another solution to only get the numerical value of the last consecutive number block could be:

LOAD *,
    Num(Mid(String,FindOneOf(String, PurgeChar(String,'-0123456789'), -1)+1)) as Number
INLINE [
String
test 001
test 002
test 010
test 013
test 234
Text With Four Spaces 012345
TextWith Three Spaces 0012346
TextWithTwo Spaces 00012347
TextWithOneSpace 000012348
TextWithoutSpaces0000012349
5
06
070
Just a textwithout numbers
];

hope this helps

regards

Marco

[#5]

Thanks, this is the only solution that worked 100%

[MarcoWedel]

You're welcome.

Glad you liked it.

regards

Marco