• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hyphens in excells

M

misia81

New Member
Hello,


I'm trying to consolidate health care data from multiple hospitals into one worksheet. However, some of my numerical data contains hyphenated numbers (i.e. 10-20 instead of 15). Is there a formula that can turn this range into one averaged number or do I have to manually change these cells to averages on my own and then consolidate?


Thank you so much for your help!

Michelle
 
Try this


=IF(ISNUMBER(FIND("-",A2)),LEFT(A2,FIND("-",A2)-1)+FLOOR((RIGHT(A2,LEN(A2)-FIND("-",A2))-LEFT(A2,FIND("-",A2)-1))/2,1),A2)
 
it worked! thank you so much for lending me your big brains :) i really appreciate your taking the time to pass on the formula.


best,

michelle
 
You must log in or register to reply here.
Back
Top