How can I extract the second half of addition ?

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

Try this:

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

--
Biff
Microsoft Excel MVP


"Gilbert DE CEULAER" wrote in message
...
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

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,

"Gilbert DE CEULAER" wrote:

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

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,





"franciz" wrote:

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,

"Gilbert DE CEULAER" wrote:

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

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

"T. Valko" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"Gilbert DE CEULAER" wrote in message
...
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

try this

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

On Sep 28, 10:04*pm, "Gilbert DE CEULAER"
wrote:
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

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

"franciz" wrote in message
news
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,

"Gilbert DE CEULAER" wrote:

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

On Sun, 28 Sep 2008 19:04:54 +0200, "Gilbert DE CEULAER"
wrote:

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

Are those entries actual *working formulas*?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gilbert DE CEULAER" wrote in message
...
Sorry, Biff, but if A1 = "=7+2", the result is 0, where I want "2" or "+2"

"T. Valko" wrote in message
...
Try this:


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

--
Biff
Microsoft Excel MVP


"Gilbert DE CEULAER" wrote in message
...
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

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

"muddan madhu" wrote in message
...
try this

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

On Sep 28, 10:04 pm, "Gilbert DE CEULAER"
wrote:
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