excelexcel-formula

Showing like number format of two numbers separated by dashes in Excel

my excel data

I have data written like added picture that is text format, for this reason i can't draw a graph. By the way, i update this excel table day by day via copy-paste from any website as this format. How can change text format to number format in this table?

enter image description here

Solution

  • Use two formula to extract the left and right parts of the string.

    If the string is always like ###-### then

    Low Range Formula

    =--LEFT(A2,3)

    High Range Formula

    =--MID(A2,5,3)

    If the string is like One or More Digits-One or More Digits then

    Low Range Formula

    =--LEFT(A4,FIND("-",A4)-1)

    High Range Formula

    =--MID(A4,FIND("-",A4)+1,999)

    Note: the =-- prefix converts the extracted string to a number (assuming it's numeric)