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.
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...