Excel Summing Un-Formatted Numbers-CodePudding

I'm trying to find an average of a large array of candidates compensation. Some of the cells contain text with multiple numbers showing a range such as, "$100k - $120k". Others are labeled as TC("120k TC") for total composition.

How would I be able to find the average of these numbers by using a something along the lines of substituting letters or parsing the string into a number WITHOUT changing the actual values listed? I do NOT want to mutate the original cell value of I only want to find an average of them all through a formula to bypass the additional "k", "TC" and "-" rendering them un-averageable as they are not parsed as numbers.

CodePudding user response：

Would need to clean up the texts in stages.

find if a certain text is present: eg.

 =IF(IFERROR(FIND("-",A1,1),"")<>"","- is present","")
 =IF(IFERROR(FIND("TC",A1,1),"")<>"","TC is present","")
 =IF(IFERROR(FIND("$",A1,1),"")<>"","$ is present","")

then split left and right price values if "-" is present: eg.

=LEFT(A1,FIND("-",A1,1))
=RIGHT(A1,FIND("-",A1,1))

then if texts are present, remove those texts: eg.

 =SUBSTITUTE(A1,"-","")
 =SUBSTITUTE(A1,"$","")
 =SUBSTITUTE(A1,"k","")

then can use trim() to remove spaces on ends, value() to convert text to number etc...