the numbers a with light with green point in the corner in the cell ,excel can't regard it

[hazemrezo]

DSC_1521.JPG

hey '

as you can see in the attached , when i use the formula with vlookup the excel can read the number which is not has green in corner in the cell how to convert this numbers to be like the first 8 numbers 32567.1 to 32567.8 but 32567.10 has green in the cell in its top right corner



Thank you very much

[oeldere]

use format of data with 2 decimals, and then do the vlookup.

the green in the corner indicates the value in the cell is text.

if this is not an answer to your question:

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

[hazemrezo]

hey i can't got it as you said , could you please find the attached my excel and edit on the file

[oeldere]

which of the 4 sheets has the question and where do I find the expected anser?

[hazemrezo]

as you can see i need to convert the numbers in the first column sheet 6

[oeldere]

you get #N/b in cel C17 (red cell), since the data in not available in the other list.

To change the other list use CTRL+H
find . (dot)

replace by , (comma)

after that use VLookup to find the data.

See the attached file.

[hazemrezo]

if you hit double tap at the cell which has the green top corner it will be white and move from left to right

i need it like that but without hitting double click

[AliGW]

Select the first cell with a green triangle and then shift select the rest of the column. With the cells still selected, scroll back to the top of the selection and click on the yellow diamond to the top left of the selection - select convert to number. Do the same for each column, always starting with the first cell showing a green triangle (e.g A20 in column A).

[hazemrezo]

i did the same the number 32562.10 will be 32562.1

[oeldere]

did you tried the CTRL+H part?

Since it worked for me.

[AliGW]

i did the same the number 32562.10 will be 32562.1

So reformat the column to two decimal places, then.

[hazemrezo]

Really i donot recognize what you did exactly Oeldere

I tried to find out what you did , but not understand

[hazemrezo]

how to reformat the column to two Decimal places .. can you clarify to me AliGW

[AliGW]

Select the column and set the formatting to number, two decimal places. Use the buttons on the home ribbon.

[oeldere]

Sheet 6

b2 =VLOOKUP($A2,Soaad!$A$2:$B$1000,2,0)

in cel b17 you get a failure since there is no 7937,16 in the sheet Soaad.

There is 7937.16 (with a dot) in sheet Soaad.

So the value is not equal, (dot is not the same as comma).

In that case you need to change the . (dot) in the sheet Soaad to a , (comma).

If you do so the formula will work.

You can do that manualy, but also with CTRL + H as already added in earlier post.

[hazemrezo]

Oelder I do apreciate what you did , but what excety i need to remove the green point in any condition ,,, we can run the formula with any zigzag way .

[oeldere]

Select whole column A.

Then try the solution with CTRL + H

find . (dot)

Replace by , (comma)

Replace all.

Then you have a list in column A with no (dots) and a , for decimals.

If you do so on the other worksheet, the values are equal (all uses comma instead of dot).

Then you can use other excel formula, to find those values.