extract and sum numbers from a text string

**Shazwb** · 08-28-2011, 08:09 PM

Hi

This is my first time posting - so I hope I've picked the correct section to ask for help.....

I have three cells containing the following information;

CLY-7.5 CLY-5.0 CLY-2.0

Sometimes the text string will change, however there will always be a dash and then one number with one decimal point at the end ie;
LSL-5.0 or A/L-6.3 or CLN-4.5 etc...

I would like a formula that will calculate 7.5 + 5 + 2 = 14.5

Can anyone please help?

Many thanks

**scottylad2** · 08-28-2011, 08:13 PM

You need to change the title of your post to something more appropriate, perhaps along the lines of extract and sum numbers from a text string.

While your doing that, can you clarify will the text strings always be the same format above ie 3 letters, a - then 2 numbers seperated by a decimal which then repeats over and over? or will the format change?

**Shazwb** · 08-28-2011, 08:32 PM

Thank you for helping me out - I've fixed the title of my post but you've asked me to PM you when I've changed it but I don't know what PM means?

**scottylad2** · 08-28-2011, 08:40 PM

If you click on the text Tevlyns name, a drop down will appear and you can send a private message telling him you've fixed the post. You'll get the fix your looking for in no time, the reason for the proper title requirement is other members may search for a solution to a similar problem they have and with the correct wording in the thread title there's every chance they'll find an answer

**rwhidden** · 08-28-2011, 08:52 PM

Originally Posted by Shazwb

Hi

This is my first time posting - so I hope I've picked the correct section to ask for help.....

I have three cells containing the following information;

CLY-7.5 CLY-5.0 CLY-2.0

Sometimes the text string will change, however there will always be a dash and then one number with one decimal point at the end ie;
LSL-5.0 or A/L-6.3 or CLN-4.5 etc...

I would like a formula that will calculate 7.5 + 5 + 2 = 14.5

Can anyone please help?

Many thanks

This works : =RIGHT(A1,3)+RIGHT(B1,3)+RIGHT(C1,3)

**Shazwb** · 08-28-2011, 09:24 PM

This works if the text is always the same but not when the text changes.

I'm not sure but it may be an if statement - if the text is CLY then sum the numbers or if the text is LSL then sum those numbers together.

Sorry, I haven't been very clear (I think I mentioned this is my first ever post...) so I've now attached what I'm trying to work out so it may be easier for you to look at.

H6 is an example of where the formula should work

Thank you