Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Extract the number


Mariano Peterson

Recommended Posts

Does anybody have a calc that extracts the number from a field with both text and numbers?

 

Ex.

mariano12345 -> becomes -> 12345

test0001 -> becomes -> 0001

 

It is safe to assume that the input will always begin with text and end with the number, and there will never be text after the number.

 

Thanks for your help everybody.

Link to comment
Share on other sites

I've devised an ugly workaround... but would still like to hear alternatives.

 

In the meanwhile, this how I solved it:

(input is the field with text and numbers)

 

This calc returns just the numbers:

 

Substitute( input , Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( input, "9", ""), "8", ""), "7", ""), "6", ""), "5", ""), "4", ""), "3", ""), "2", ""), "1", ""), "0", ""), "")

 

This calc returns just the text:

 

Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( input, "9", ""), "8", ""), "7", ""), "6", ""), "5", ""), "4", ""), "3", ""), "2", ""), "1", ""), "0", "")

Link to comment
Share on other sites

Thanks Michael! That got me to thinking, and I rewrote my calculation.

 

I think the first calc is more efficient, but this second one (below) handles the situation where special characters are mixed in to the right of the first number. (I just found out that there can be special chars mixed in with the number, for example, mariano123.456 fails with the first calc) Thanks again for your help! laugh.gif

 

The new calc to get text to the right of the first number:

 

Right( input, Length( input ) -

Min(

If( Position( input, "0", 0, 1) > 0, Position( input, "0", 0, 1), 65000),

If( Position( input, "1", 0, 1) > 0, Position( input, "1", 0, 1), 65000),

If( Position( input, "2", 0, 1) > 0, Position( input, "2", 0, 1), 65000),

If( Position( input, "3", 0, 1) > 0, Position( input, "3", 0, 1), 65000),

If( Position( input, "4", 0, 1) > 0, Position( input, "4", 0, 1), 65000),

If( Position( input, "5", 0, 1) > 0, Position( input, "5", 0, 1), 65000),

If( Position( input, "6", 0, 1) > 0, Position( input, "6", 0, 1), 65000),

If( Position( input, "7", 0, 1) > 0, Position( input, "7", 0, 1), 65000),

If( Position( input, "8", 0, 1) > 0, Position( input, "8", 0, 1), 65000),

If( Position( input, "9", 0, 1) > 0, Position( input, "9", 0, 1), 65000)

) + 1

)

 

The new calc to get text to the left of the first number:

 

Left( input,

Min(

If( Position( input, "0", 0, 1) > 0, Position( input, "0", 0, 1), 65000),

If( Position( input, "1", 0, 1) > 0, Position( input, "1", 0, 1), 65000),

If( Position( input, "2", 0, 1) > 0, Position( input, "2", 0, 1), 65000),

If( Position( input, "3", 0, 1) > 0, Position( input, "3", 0, 1), 65000),

If( Position( input, "4", 0, 1) > 0, Position( input, "4", 0, 1), 65000),

If( Position( input, "5", 0, 1) > 0, Position( input, "5", 0, 1), 65000),

If( Position( input, "6", 0, 1) > 0, Position( input, "6", 0, 1), 65000),

If( Position( input, "7", 0, 1) > 0, Position( input, "7", 0, 1), 65000),

If( Position( input, "8", 0, 1) > 0, Position( input, "8", 0, 1), 65000),

If( Position( input, "9", 0, 1) > 0, Position( input, "9", 0, 1), 65000)

) - 1

)

Link to comment
Share on other sites

Maybe I'm being too simple, but don't number fields in FM ignore non numbers? Couldn't you make a calculation like

 

RESULTFIELD= SAMPLEFIELD * 1 / 1

 

If the result is set to number it would only include the numbers.

 

You might want to get rid of numeric specials like "-" or "." and I guess these are so limited that you could use ABS for the "-" and/or a replacement script step.

Link to comment
Share on other sites

Hi,

 

If you change the field to a number, then run either Set Field (MixedTextNumField, MixedTextNumField), or Replace Contents via calculation and just enter the field name again, it will strip the text. Back up first, though! smile.gif

 

LaRetta

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use