| View previous topic :: View next topic |
| Author |
Message |
aloarjr810
OOo Enthusiast
Joined: 04 Jun 2008
Posts: 113
|
 Posted: Mon Jun 07, 2010 9:41 am Post subject: [Solved] adding to numbers with a char inbetween the numbers |
 |
|
Hope I phrased that right.
what I'm looking for is to be able to put in cell A1 this 1&2 or 13&14 etc.
then in cell B2 and the following cells have it say 3&4,5&6 etc. or 15&16,17&18 etc
I remembered this post "How to sum cells that have a letter in the cells?" but couldn't quite adapt it to work.
I came up with this formula, which I think is close but I'm not getting the whole number.
| Code: | | =(LEFT(A2;1)+2)&"&"&RIGHT(A2;1)+2 |
I've tried leading zeros 01&02 and varying the other parameters, but don't quite get the right result.
Last edited by aloarjr810 on Mon Jun 07, 2010 10:49 am; edited 1 time in total |
|
| Back to top |
|
 |
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Mon Jun 07, 2010 10:27 am Post subject: |
|
|
=(LEFT(A2;(FIND("&";A2)-1))+2&"&")
seems to get the number before the ampersand.
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Mon Jun 07, 2010 10:41 am Post subject: |
|
|
Try:
=LEFT(A2;(FIND("&";A2)-1))+2&"&"&RIGHT(A2;LEN(A2)-FIND("&";A2))+2
for the whole lot.
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
aloarjr810
OOo Enthusiast
Joined: 04 Jun 2008
Posts: 113
|
| Posted: Mon Jun 07, 2010 10:49 am Post subject: |
|
|
| Robert Tucker wrote: | Try:
=LEFT(A2;(FIND("&";A2)-1))+2&"&"&RIGHT(A2;LEN(A2)-FIND("&";A2))+2
for the whole lot. |
Thanks RT! that seems to work ok. |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8995
Location: Lexinton, Kentucky, USA
|
| Posted: Mon Jun 07, 2010 10:50 am Post subject: |
|
|
Very nice Robert. I went at it with a user defined function. | Code: | Global l,r
Function AdvanceNumbers
If l = 0 then
l = 1
Else l = l + 2
EndIf
r = r + 2
AdvanceNumbers = l &"&"& r
End Function |
|
|
| Back to top |
|
|
|
