If you mean that there may be many spaces in a row (for example "5 3 1/2"), it's true that will cause a problem in my method. I don't see that in your data, though. In your data what I see is just one space between the base and the power, and then in some cases another space in the power itself, like this: "5 3 1/2". So all you need to do is first separate the base from the power using a FIND, LEFT and MID; then separate the power into its parts.

Two problems, though:

1) Whatever is between the base and its power, it isn't a space. I just looked more closely, and it's an invisible character with the decimal value of 160. I don't know why that's there, but it's easy enough to deal with. I can show you how.

2) The method I used involves several helping columns on every line. And now, because you have to separate the base from its power, there will be several more, probably 10 or 12 in all. That's a lot of extra columns. Perhaps writing a VBA worksheet function would be better.

Take a look at question-2.xls. I have written a VBA worksheet function that I called Question. In Sheet1 I've applied it to your sample data; I think it calculates everything correctly. Although in the end you may decide not to use this, it's much simpler than all those extra helping columns. But...it's simpler only if you are willing to use a VBA solution, which is sometimes not the best way to do things. If you'd rather use the formulae, let me know and we'll go back to that.

Attached Files

question-2.xls (41.5 KB, 10 views)