Splitting hyphen-separated data into multiple cells

Data > Text to columns, Delimited ...

Thanks shg. That is the method I've been using. I am trying to use formulas, so I can paste my data into a WIP tab and have the useful data populated into my formulated/formatted sheet.

This is where I got the idea for the SEARCH and position formulas: https://support.office.com/en-ca/art...c-1bf66e219ea8

Row\Col	A	B	C	D	E	F
1	186.00000000 - 130.00000000 - 76.00000000 - 40.00000000	186.00000000	130.00000000	76.00000000	40.00000000	B1: =TRIM(MID(SUBSTITUTE($A1, "-", REPT(" ", 100)), 100*COLUMNS($B1:B1) - 99, 100))
2	30.00000000 - 39.00000000 - 24.00000000 - 4.00000000	30.00000000	39.00000000	24.00000000	4.00000000
3	226.00000000 - 323.00000000 - 122.00000000 - 96.00000000	226.00000000	323.00000000	122.00000000	96.00000000
4	6.00000000 - 1.00000000 - 5.00000000 - 5.00000000	6.00000000	1.00000000	5.00000000	5.00000000

=trim(mid(substitute($a1, " - ", ...

You're welcome.

If I overwrite the data in both columns 1 and 2, the slope formula works (for those two data points).
Attachment 364834

I didn't look at your file.

The formulas to split the data are returning TEXT values even though they look like numbers.

One way to convert them to numbers is use the double unary minus operation.

--(formula)

The inner - converts the text number to a negative numeric value.

The outer - just reverses the numbers sign, from negative to positive.

You're welcome. Thanks for the feedback!