Diaplaying only numeric values in a cell with alpha numeric characters

[kiran2512]

Hi,
I require code for displaying only numeric values and special characters "." and "," in a particular cell which has alpha numeric characters and other special characters.

For Example: Cell A1 is given a text input as "HIGH : 1,234.56", and Cell B1 is given text input as "LOWS : 1,111.22". I want the values to be displayed as 1,234.56 and 1,111.22 in their respective cells.

Is there any way to use the data in a cell which is derived out of a formula in another cell. When i try to get the data in a cell(derived out of a formula) to another sheet, i only get the formula but not the data.. is there a way to mask the formula so that only the data in the cell can be sent to other sheets by providing a path...when manually copying the content we use Paste special to get only the values...is there any way to do it automatically as well..

Please help me on this as well..

 

[Tom Urtis]

I do not understand your paragraph that starts with "Is there any way...". Not sure what you want there.

Regarding your first question, it seems (based on your posted sample set) that you can employ a formula to extract those numbers and characters, based on identifying the first found number, and extracting it and everything to the right of it, as exampled by the following picture.

The formula the formula in cell D2 and copied down to cell D13 is
<CODE>=TRIM(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,""))</CODE>

 

[kiran2512]

Thank u for the formula... it helped...

Let me explain my second question with an example.

In the sheet you have provided above, the values in D2 to D13 are arrived out of a formula. If i want to use the values in D2 to D13 in a formula in column F from F2 to F13.... it shows a error as "#VALUE!" as the column D has a formula in it. How do i use the values in column D for the formula in column F without getting the error...

Hope i am clear now...

 

[Tom Urtis]

Well, still not totally clear. How about posting the formula you are attempting to enter into column F and that will provide a clue as to why you are getting a #VALUE error. It will also help if you say what the purpose of the formula is for column F, and what your expected results are of that formula.

 

[kiran2512]

I will provide you with the exact scenario...

In the Column A and Column B i have values as "HIGH : 5633.20" and "LOW : 5578.90". Using the formula which you have provided i get the value as "5633.20" and "5578.90" in columns I and J respectively. Column K is the difference of I and J which has the formula as "=I:I-J:J".
In column L i have a formula as =IF(K:K<G:G,J:J+G:G,IF(AND(K:K>=G:G,K:K<=H:H),J:J+H:H,IF(K:K>H:H,"TRUE","FALSE"))) and in column M formula as =IF(K:K<G:G,I:I-G:G,IF(AND(K:K>=G:G,K:K<=H:H),I:I-H:H,IF(K:K>H:H,"True","False"))).
Values in Columns G and H are also derived out of the formulas =ABS(F:F*0.45) and =PRODUCT(F:F*0.75) respectively.

The formula in columns L and M is filled in all the blank cells and the cells show 0 when all the other cells(G, H, K, I, J) are blank or 0. But when i drag the formula down in columns I and J, i get the #value error in columns L and M. All the blank cells with formula in columns L and M reflect as "#VALUE!". I want the cells with formula in column L and M to appear blank or "0" as they appeared before i entered the formula in columns I and J.