+ Start a Discussion
Kenn K.Kenn K. 

Extracting number values in a text field then summing up

Hi there,

 

I have this field that gets populated by an external service and I want to extract the number values from it then sum the numbers up.

For instance, see the text below

United Kingdom: 3, Ireland: 1, Netherlands: 1, Spain: 2, Australia: 1

 

I would like to extract 3,1,1,2,1 and sum them individually to have 8. i.e. 3+1+1+2+1 = 8.

 

Thanks.

ReidCReidC

There might be a way to do this in a formula that escapes me right now, but you could do it in Apex by splitting the text into it's components, calling isAlpha on individual pieces, and only keeping the letters.  The exact process will depend on what the text looks like.

 

http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#StartTopic=Content/apex_methods_system_string.htm

Kenn K.Kenn K.
Thanks Reid. I am not even sure how to splice all this to take advantage of the functions. This will be a challenge.
Kenn K.Kenn K.

This java function helped!

 

LinkedList<String> numbers = new LinkedList<String>();

Pattern p = Pattern.compile("\\d+");
Matcher m = p.matcher(line); 
while (m.find()) {
   numbers.add(m.group());
}

 

 

ReidCReidC

I love it.  I'm kinda crap with regex, so I forget about them.

Coincidentally I'm parsing through a bunch of crappy data right now, and also decided I would do this to build a clean string of numbers from a potentially mixed bag:

 

ret = ret.trim();
if (!ret.isNumeric()) {
String newRet = '';
for (Integer i = 0; i < ret.length(); i++) {
String t = ret.substring(i, i+1);
if (t.isNumeric()) {
newRet += t;
}
}
ret = newRet;
}

 

Kenn K.Kenn K.

That's a good approach. Great, Thanks!