excel - Formula to Provide Total for Numbers Combined with Text -

i having problems getting total row of cells contain both numbers , text, have tried couple of different formulas , don't fix it.

i have 5 cells in row reference sum total in separate workbook add text on to: ='k:\folder 1\surveys\returned surveys[00001.xlsm]00001'!$d$15 & "/18"

at first had =sum(k4:o4) returned 5 instead of sum total of row.

i tried find number within text following formula: =left(find("/",k4)-1+(find("/",l4)-1+(find("/",m4)-1+(find("/",n4)-1+(find("/",o4))))))-1 however, returns #value.

in last ditch attempt tried convert values text , add them returns number of cells rather total value: =text(k4,0)&"/18" , above =left formula.

select 5 cells , use simple equals formula in each. example:

='k:\folder 1\surveys\returned surveys[00001.xlsm]00001'!$d$15 

with 5 cells selected, tap ctrl+1. when format cells dialog opens, go number tab. choose custom list down left , supply following type:,

0\/18 

... or,

0\/12 

click ok. numbers look have /12 or /18 tacked on right side underlying values still raw numbers external workbook. sum them using conventional means want (r.g. =sum(k4:o4) or =k4+l4+m4+n4+o4).

your formula have worked as,

=left(k4, find("/", k4)-1)+left(l4, find("/", l4)-1)+left(m4, find("/", m4)-1)+left(n4, find("/", n4)-1)+left(o4, find("/", o4)-1) 

the following image shows method (concatenate split) in row 4 , mine (custom number format , sum) in row 5.

        

text defaults left-alignment , true numbers default right-alignment.