Salesforce and other SMB Solutions are coming soon. ×

# Extract the number

## 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.

##### 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", "")

##### Share on other sites

Mariano,

Check this thread it may be of some use. Worth a look anyways.

Michael

##### 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!

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

)

##### 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.

##### 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!

LaRetta

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...