Showing like number format of two numbers separated by dashes in Excel
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?
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)
- Excel VBA - Skip sub or end sub IF Statement
- What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns" in Power Query?
- Find all matches that use the 4 digits in sheet1 to find the 10 digit phone numbers by matching the last 4 numbers in sheet2
- Excel VBA- Loop Through Target Cells in a Table for Tracking Changes
- VBA Concatenate 2 columns without loop
- Excel: What should iferror return in order to avoid #value! error
- Excel VLOOKUP for 3 columns
- Excel: How to check if a value that meets the specified criteria exists in a lookup table?
- Showing like number format of two numbers separated by dashes in Excel
- Excel distinct count with conditions
- Different background colors for even and odd rows
- excel adding 1 on all alphanumeric string
- For an Excel Formula's variable taking a Range, input row number by the result of a formula
- how to change the text number automatically in excel without retype the formula?
- Excel: Find last value in an array
- Combining formulas
- How to skip calculating a cell if its left cell has an error value
- Power Query - splitting column
- Getting "At least one sheet must be visible" when trying to open excel spreadsheet. What to do?
- Update cells in a range based on cells in a range of a different dimensions, using VBA
- Timer on user form in Excel VBA
- Generating CSV file for Excel, how to have a newline inside a value
- Circular Reference in Dynamic Array Formula
- Excel array formula that references previously calculated values within the array
- Detect bold texts in a cell and add ";" before those texts
- Convert an array of bytes into a string?
- Deleting rows from Excel sheet based on the values in column A and Column H
- Dir Function to find file
- How to add corresponding values to a different sheet with duplicate values (while keeping the duplicates intact)?
- Find row when Cumulative sum reaches certain value with condition