I am trying to get what ever is to the left of the space in cell A1 and put it in sheet1 cell C1.
in sheet2 A2 is 223.34
In sheet1 cell A1 I have a formula =ROUND(Sheet2!A2,0)&" "& (123)
this displays 223 (123) in cell A1.
I would like to retreave what ever is to the left of the space in A1 and put it into C1, in this case 223 but next time it could be 23 or 2223

I know I could put a formula into C1 along the lines of =sum(sheet2!a2) but I wish to avoid this. A macro will do it but again wish to avoid the use of a macro at this point

smiler44

It would appear that what is to the left of the space is =ROUND(Sheet2!A2,0)
I assume that I have missed the point by suggesting placing that in C1

Brian

I managed to get the whats to the left of the space sorted but now need to push the boundaries a bit more. It now seems I need to get what is in between the brackets of 223 (123) both the 123 and 223 may change in length.

=MID(A1,FIND(" ",A1),LEN(A1)) gives me what is in the brackets as well as the brackeets

=RIGHT(A1,FIND("(",A1)-1) gives me 123)

I can not get any better then that. Looking at formula examples I'm sure that just the 123 can be gained but I cant work out the formula.
smiler44

It would be polite to say how you had resolved your problem rather than just ignore respondents, the solution to your new problem is simple, you were almost there.

=MID(A1,FIND("(",A1)+1,LEN(A1)-(FIND("(",A1)+1))


Brian

Brian, not sure if to appologise first or say thank you your a genius.
I am truly sorry for seeming to ignoring your advice. I would not want to offend anyone that takes the time to help me.
I think I solved it by using =LEFT(A2,FIND(" ",A2))
It has been pointed out to me that it should be =LEFT(A2,FIND(" ",A2)-1) so the space between the " " is not included.
I can now add whats to the left of the space and whats to the right of the space,but now i see I have another problem.
after the adding whats to the left of the space it displays in the cell as 223 but whats to the right needs to be in brackets. so I end up with 223 (123) format.
if I wanted to add up 6 cells it would be a long formula so below is a formula to "add" just one cell and put the answer into B1.
=LEFT(A1,FIND(" ",A1)-1) & " " &MID(A1,FIND("(",A1)+1,LEN(A1)-(FIND("(",A1)+1))
the formula gives me 223 123 but i would like 223 (123)
cell a1 223 (123)
cell a2 234 (345)
cell a3 321 (650)

answear in B1 778 (1118)
I got all the way to here before a flash of insperation and getting
=LEFT(A1,FIND(" ",A1)-1) & " " &"("&MID(A1,FIND("(",A1)+1,LEN(A1)-(FIND("(",A1)+1))&")"
I do try and solve my problems, but can it add the 6 cells? only time will tell!

smiler44

No it cannot add the cells, although I wait to be proved wrong.
You will need to extract the 2 text numbers from each cell and convert them to real numbers by *1 eg =LEFT(A1,FIND(" ",A1)-1)*1 then add them up ie Sum(b1:b6) and recombine. Say you do the first part into col B and the second into col C then make D1 =B7 & " (" & C7 & ")"

The alternative would be a user function

Brian

Seems to work with 123 (234) and 134 (290)
=LEFT(A1,FIND(" ",A1)-1) +LEFT(A2,FIND(" ",A2)-1) & " " &"("&MID(A1,FIND("(",A1)+1,LEN(A1)-(FIND("(",A1)+1))+ MID(A2,FIND("(",A2)+1,LEN(A2)-(FIND("(",A2)+1))&")"


have added a 3rd cell and formula and it was ok and have messed around with the numbers such as 5 (8) still ok.

I would like to say I'm right but I'm going to wait to see if I'm proved wrong.

With your help Brian this may be the cracking of this problem.

User function, never created one.

smiler44

User function

Alt+f11 to open up VBA
Select your workbook in the left hand pane
select Insert - module from the menu
select the module
start work by typing in the right hand pane, in this case copy and paste in this code

Private Function faddcells(myrange As Range) As String

Dim totalnumber1 As Long
Dim totalnumber2 As Long

For Each c In myrange
totalnumber1 = totalnumber1 + Left(c, InStr(c, " ") - 1) * 1
totalnumber2 = totalnumber2 + Mid(c, InStr(c, "(") + 1, Len(c) - (InStr(c, "(") + 1)) * 1
Next c
faddcells = totalnumber1 & " (" & totalnumber2 & ")"
End Function

I always precede my function names with f others use bas for historical reasons, don't ask, but some kind of convention is useful to differentiate your functions from MS functions.

To run the above code =faddcells(a1:a6) in whatever cell, the advantage is any range is easily altered.

I have not included any error checking, experts like chergh always do but amateurs like me often don't.

Brian

PS you will notice that I don't need the *1 as I am not using the Sum function, which is why you don't in your approach. I did not take that approach as it seemed heavy and difficult to alter if the cell range changed, but maybe that is not a problem.

Brian,
I have tried your function. It did add up what was to the left of the space and put it in the left hand side of the cell that had =faddcells(A1:A6) but it also put the total in brackets to the right but in the same cell. I will study the function a bit more to try and understand it.

Thank you

smiler44

Brian,
I have tried your function. It did add up what was to the left of the space and put it in the left hand side of the cell that had =faddcells(A1:A6) but it also put the total in brackets to the right but in the same cell. I will study the function a bit more to try and understand it.

Thank you

smiler44

I thought that that was what was required.

Brian

Not quite. Where I have 133 (345) in cell A1 and 456 (213) in cell A2 for example
I need to add the 133 and 456 and have this in the left of say cell A3 and then add the 345 and 213 and put the answer in brackets on the right side of cell A3 so I get
589 (558) in cell A3. In fact they have changed ther minds and 133 and 456 will be in column A cnd 345 and 213 will be in column B making adding up easy. It also look neater.

smiler44

Not quite. Where I have 133 (345) in cell A1 and 456 (213) in cell A2 for example
I need to add the 133 and 456 and have this in the left of say cell A3 and then add the 345 and 213 and put the answer in brackets on the right side of cell A3 so I get
589 (558) in cell A3.
smiler44

That is precisely what my function does, and just incase I was wrong I have just tested it again with those figures, however I see that your mad management have changed their minds, I get the impression that they do that frequently, and you wont be needing a function now.

Best of luck.

Brian

Alas your right I no longer need a function but I'd like to learn about them.
I've just tried your function again and it worked. I don't know what I did last time, sorry for doubting, I should have known better:)

Smiler44