How can I extract the second half of addition ?

[Gilbert DE CEULAER]

I have lots of entries that have the form "+6+2", "+4+3", "+5+1".
Is there any way to extract the second half, so the "+2", "+3", "+1" - part
?
(Not all of the entries concerned are like that; somre simply read "+6",
"+3", "+2"; in that case the extraction should read "0")
Thanks in advance,
Gilbert

 

[T. Valko]

Try this:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))<2,0,MID(A1,FIND("+",A1,FIND("+",A1)+1),10))

 

[franciz]

Assuming your data is in col A, you may use the Right function to extract
"+2","+3","+1" from your examples given.

place this in B1 and drag down as far as you need

=RIGHT(A1,2)

Does this do what you want?

regards,

 

[franciz]

I have expand the formula to include "0" if your data shows only "+6" instead
of
"+6+2"

Place this in B1 and drag down

=IF(RIGHT(A3,2)=A3,"0",(RIGHT(A3,2)))

Hope this is of help

regards,

 

[Gilbert DE CEULAER]

Sorry, Biff, but if A1 = "=7+2", the result is 0, where I want "2" or "+2"

 

[muddan madhu]

try this

=IF(ISERROR(MID(A1,FIND("+",A1,2),255)),0,MID(A1,FIND("+",A1,2),255))

 

[Gilbert DE CEULAER]

Sorry, Franciz, but "=4+4" gives "8", instead of "4" or "+4"
And what about the entries without a second part ?

 

[Ron Rosenfeld]

I have lots of entries that have the form "+6+2", "+4+3", "+5+1".
Is there any way to extract the second half, so the "+2", "+3", "+1" - part
?
(Not all of the entries concerned are like that; somre simply read "+6",
"+3", "+2"; in that case the extraction should read "0")
Thanks in advance,
Gilbert

You could use a User Defined Function.

This assumes you have provided inclusive examples of the format of your
entries. If you have not, we will need to change re.Pattern.

pattern: Quotes | + | digit(s) | + | digit(s) | quotes | end-of-line

<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

Insert/Module and paste the code below into the window that opens.

Use a formula =LastPlus(cell_ref) where cell_ref is the address of a cell
containing your string.

==================================
Option Explicit
Function LastPlus(str As String) As Variant
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\d)(\+\d+(?=""$))"
If re.test(str) = True Then
Set mc = re.Execute(str)
LastPlus = mc(0).submatches(1)
End If

End Function
=================================
--ron

 

[Ragdyer]

Are those entries actual *working formulas*?

 

[Gilbert DE CEULAER]

Sorry, Muddan, but "=4+2" gives 0, instead of "2" or "+2"

try this

=IF(ISERROR(MID(A1,FIND("+",A1,2),255)),0,MID(A1,FIND("+",A1,2),255))

 

[Gilbert DE CEULAER]

Dear Ron,
Actually, when I enter +4+2 in a case, and when I look at it afterwards, it
shows =4+2.
Does that change antything in your solution ?

 

[Gilbert DE CEULAER]

I enter +4+2, and when I look at the case afterwards, it reads =4+2
Does that help ?

 

[Gilbert DE CEULAER]

Dear RD,
When I enter +4+2, and read the case afterwards, it shows =4+2
Does that answer your question ?

 

[T. Valko]

Well, you didn't mention that the entries might contain an = sign.

 

[Gilbert DE CEULAER]

Sorry, Biff, but when I enter +2+1 the case shows =2+1

 

[T. Valko]

Ok, so you're entering *fomulas*, not TEXT strings.

You're going to need VBA code to do this. I see Ron has responded and is on
the right track.

 

[Ron Rosenfeld]

Dear Ron,
Actually, when I enter +4+2 in a case, and when I look at it afterwards, it
shows =4+2.
Does that change antything in your solution ?

Well, of course.

There is a great deal of difference between "+4+2" which is a string; and =4+2
which is, in your case, a formula.

So we need to examine the formula text, and not the strings which you posted.

So a slight change in the routine:

=======================
Option Explicit
Function LastPlus(rg As Range) As Variant
Dim re As Object, mc As Object
Dim str As String
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d(\+\d+$)"
str = rg.Formula
If re.test(str) = True Then
Set mc = re.Execute(str)
LastPlus = mc(0).submatches(0)
End If
End Function
=========================
--ron

 

[T. Valko]

No need to apologize!

You should get out of the habit of starting a *formula* with the + sign and
use the = sign which is the standard for Excel.

We all thought that "+2+2" was in fact a TEXT string so none of our
suggestions will work when these are actually formulas. You're going to need
a VBA code solution.

See Ron Rosenfeld's suggestion. He seems to be on the right track and is
very good at "string" parsing.

 

[Gilbert DE CEULAER]

Thanks, Ron, I do not understand the code... but it works.
Gilbert

 

[Ragdyer]

When you say "read the case afterwards",
What exactly do you mean by "case"?

If you're entering formulas, and you see what you typed in the cell, and XL
is changing (correcting) your entry, try hitting
<Ctrl> < ` > (shares key with ~ )
OR, from the menu bar,
<Tools> <Options> <View> tab,
And *Uncheck* "Formulas" under window options.

If you're seeing the corrected formula *only* in the formula bar,
them you have a choice of parsing the formula with code, OR with XL 4.0
macros.

Here's an old post that describes the XL 4.0 procedure.

http://tinyurl.com/46af9t

If you wish to pursue this type of procedure, you can post back for explicit
parsing formulas.