Dear Sir



We have attached a excel file name question
We have a lot of data as column A which represent as in fraction
and we would like to represent it as decimal representation in column C
how to write a VBA programming to change data in column A to column C
in my attached file

Thanks a lot
Tom

Attached Files

question.xls (19.0 KB, 12 views)

If your desire is simply to interpret "3 1/2" and return 3.5 (for example), then you don't have to write a VBA program to do it. You can, if you want, and we'll help you; but you can do it using worksheet functions. See Sheet2 on the attached for one way to do that.

If you still want to do it with a program—and that might be simpler, from the point of view of the person using the workbook—feel free to say so, and I'll show you how to do that instead.

Attached Files

x.xls (30.0 KB, 10 views)

Dear Bobbridges,

When we put your formula on the question as attached. It don't work (maye
the format is different and we cannot change) because we copy these information from web in excel file called "qeustion" we only need the blue color words. how we can do
We need your help

Thanks
Tom

Attached Files

question.xls (19.0 KB, 9 views)

You said you used my formula "on the question as attached"; but I'm looking at question-1.xls, and it doesn't have my formula in there. It doesn't have any formula. It looks like the same workbook you attached the first time. So I cannot see what you tried, and why it didn't work.

However, I think I know why it didn't work. Remember, I said below that my solution would work "if your desire is simply to interpret '3 1/2' and return 3.5 (for example)". The numbers in blue are the powers, ie "3 1/2", "1/2" etc. But the contents of a whole cell are the base and the power together; the cell contains "5 3 1/2", and you need a method to separate the "3 1/2" from the "5" before my formulas can work.

Do you know how to separate the two parts of the number? Post question-1.xls again, but this time include in it the formulas you've tried. It doesn't matter that they don't work; by looking at what you tried, even if it's wrong, I will probably be able to see why you don't understand the problem, and explain it to you better.

Dear Bobridges

Attached pls find the revised questions file which i put the formula and
i dont how to separate the first number ...so the formula dont work
we need your help

Thanks for your great help
Tom

Attached Files

revised question.xls (24.0 KB, 10 views)

Yes, I see the problem. But to understand the solution, you must look at the first few rows of Sheet2 and understand why they work; then you will be able to look at the following rows and understand why they don't work.

In my solution, I use three functions: FIND, LEFT and MID. Do you know what those functions do? If you don't, tell me and I will explain them. If you do—when you do—you can then study what I did in rows 1 through 4 and see how each piece of that solution works.

When you understand that, you will probably understand what you must do differently with your own data. But I will still help you with that, too. First understand what I'm doing in rows 1 through 4. Tell me how much of that you understand, and I'll explain from there.

Hi thanks for your great help to explain.Your explaination(Find,Left and Mid) is very clear and logical.step by step. it is easyier to understand

But in my data, maybe it involved many spaces between the data, so i
cannot work out the answer. i really need your great help.

Thanks for your great help
Tom

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)

Dear BobBridges

Thanks for your great help and it is really useful. we would better to use
VBA because a lot of data will involved. attached pls find the new question file which
i actual face and solve. We need your expert help. The answer will show in column
13 to 18. also there is some head,neck involved instead of number. how to do

thanks for your great help
Tom

Attached Files

new qustion.xls (21.0 KB, 10 views)

I think my solution last time was wrong. I thought you wanted it to calculate 5^(3 1/2), but you just want the superscripted part, 3.5. Then, if the superscript isn't a numeric value, you want back just the string without being changed. Is that right?

Still, you can modify my routine to make this work. Start by looking at the routine and see how it works. Then change it in this way: Detect whether the part of the text that is superscript can be viewed as a numeric value. 1) If it can, calculate the value (as I did), but return the superscript value rather than the base to that power. And 2) if it can't, don't do any calculation at all; just return the string.

I realize you may not know how to modify or even look at a worksheet function, such as the one I wrote for you in my last post. If so, don't worry; just tell me how much you understand, and I'll explain how to go on from that place.

Dear Bobbridges,

Yes u know my situation attached pls find the new questions excel files.

1. yes u are right want the superscripted part Then, if the superscript isn't a numeric value, we want back just the string without being changed. Yes retrun the string if
dont do any calculation at all

2. Actually i dont fully understand because i only attend 10hr vba course but i trust you have expert ability to do this excise

3. at this moment , the most important is that i can get the vba code to do my
routine work as file attached. Then i will study step by step later. As a matter
of fact at least i can do my job to boss in time without delay. haha

I my abiltiy i believe i cannot do such complex progamme, we really
need your help
Thanks
Tom

Attached Files

new qustion.xls (21.0 KB, 8 views)