|
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
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 |
How can I extract the second half of addition ?
Hello,
Ron's VBA solution might seem preferrable but there is a non-VBA solution: Define the name SecSumPar, for example, which refers to: =MID(GET.CELL(6,INDIRECT("RC[-1]",FALSE)),1+LOOKUP(2,1/ ("+"=MID(GET.CELL(6,INDIRECT("RC[-1]",FALSE)),ROW(INDIRECT("1:"&LEN(GET.CELL(6,INDIREC T("RC[-1]",FALSE))))), 1)),ROW(INDIRECT("1:"&LEN(GET.CELL(6,INDIRECT("RC[-1]",FALSE)))))), 999) Then insert into the next cell right to your cell with =3+4: =SecSumPar and you will get 4 Regards, Bernd |
| All times are GMT +1. The time now is 12:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com