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

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

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

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

"Ragdyer" wrote in message
...
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

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

"Ragdyer" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Gilbert DE CEULAER" wrote in message
...
Dear RD,
When I enter +4+2, and read the case afterwards, it shows =4+2
Does that answer your question ?

"Ragdyer" wrote in message
...
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

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gilbert DE CEULAER" wrote in message
...
Dear RD,
When I enter +4+2, and read the case afterwards, it shows =4+2
Does that answer your question ?

"Ragdyer" wrote in message
...
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

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

--
Biff
Microsoft Excel MVP


"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, Biff, but when I enter +2+1 the case shows =2+1

"T. Valko" wrote in message
...
Well, you didn't mention that the entries might contain an = sign.

--
Biff
Microsoft Excel MVP


"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

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.

--
Biff
Microsoft Excel MVP


"Gilbert DE CEULAER" wrote in message
...
Sorry, Biff, but when I enter +2+1 the case shows =2+1

"T. Valko" wrote in message
...
Well, you didn't mention that the entries might contain an = sign.

--
Biff
Microsoft Excel MVP


"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

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, Franciz, but "=4+4" gives "8", instead of "4" or "+4"
And what about the entries without a second part ?

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

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, 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

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

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 ?

"Ron Rosenfeld" wrote in message
...
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

On Sun, 28 Sep 2008 20:16:17 +0200, "Gilbert DE CEULAER"
wrote:

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

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

"Ron Rosenfeld" wrote in message
...
On Sun, 28 Sep 2008 20:16:17 +0200, "Gilbert DE CEULAER"
wrote:

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

On Sun, 28 Sep 2008 20:41:16 +0200, "Gilbert DE CEULAER"
wrote:

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

I'm glad it works. Thanks for the feedback.

The part of the code that you won't find in the HELP section has to do with the
regular expression engine. There is information he

http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://www.regular-expressions.info/reference.html

The re.Pattern that I am using, "\d(\+\d+$)"

matches

any digit
followed by a '+'
followed by any number of digits
followed by the end of line.

So it is looking for a pattern which matches anything like:

6+2
or
6+234

etc.

If that pattern matches, then it returns everything except the leading digit.
--ron