Solved: Number Stored as Text

[jammer6_9]

I am extracting Data from a another program and saving it to Excel. Now my problem is VLookUp formula can not read data extracted since data is save as "Number Stored as Text" How can I code it rather convert it to Number in Worksheet_SelectionChange Event using macro code.

[Bob Phillips]

Convert it in the VLOOKUP, like so

=VLOOKUP(TEXT(2,"0"),J1:K3,2,FALSE)

[Charlize]

Sub convert_text_to_number()
Dim v_no_text As Range
For Each v_no_text In Worksheets(1).Range("A2:A" & _
Worksheets(1).Range("A" & _
Rows.Count).End(xlUp).Row)
v_no_text.NumberFormat = "0"
Next v_no_text
End SuborSub convert_text_to_number()
Worksheets(1).Range("A2:A" & Worksheets(1).Range("A" & _
Rows.Count).End(xlUp).Row).Select
Selection.NumberFormat = "0"
End Sub

[jammer6_9]

Now I got 2-3 options to use and it works. :bow:

[jammer6_9]

I go with xld code wherein I did not use any macro but when the data result on the look up is "-" <-- this is suppose to be zero, i am #having N/A on my calculation where I can not sum the total.

Same as if Lookup 127 and it is not in the table #N/A result also come out.


Convert it in the VLOOKUP, like so

=VLOOKUP(TEXT(2,"0"),J1:K3,2,FALSE)

[Bob Phillips]

If i understand you correctly, that works fine for me.

[jammer6_9]

Yes it works fine for me as well BUT when the ColumnIndex Value is "-" then the result will be "-". How can I make this "-" value in the cell to "0" so that I will be able to sum up calculation.

What I want to do now is something like


If cell value = "-" then
cell value = 0
End if


Take note that the data was extracted from different program wherein if the value is "0" it displays "-" in the worksheet.

[Charlize]

?
=IF(VLOOKUP(TEXT(2,"0"),J1:K3,2,FALSE)="-",0,VLOOKUP(TEXT(2,"0"),J1:K3,2,FALSE))

[jammer6_9]

it says too many arguments


?
=IF(VLOOKUP(TEXT(2,"0"),J1:K3,2,FALSE)="-",0,VLOOKUP(TEXT(2,"0"),J1:K3,2,FALSE))

[jammer6_9]

Sorry Charlize your code works.

=IF(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)="-",0,(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)))

[Bob Phillips]

Tryt using SUM instead of manually adding

=IF(O16=0,0,SUM(G16,I16,K16))

instead of

=IF(O16=0,0,G16+I16+K16)

[jammer6_9]

Just one more thing if you will open my attachement, If the LookUp Value is not found #N/A is coming out. How can I display #N/A to "0" also...
ISERROR will work?

[Bob Phillips]

=IF(ISNA(lookup_formula),0,lookup_formula)

[jammer6_9]

I apologize :banghead: It's my first time to go with this Long kind of formula. It's pointing the "0" in the middle of lookup formula

=IF(ISNA(VLOOKUP(TEXT(A15,"0"),Database!$A$1:$AN$420,5,0),0,(VLOOKUP(TEXT(A15,"0"),Database!$A$1:$AN$420,5,0))))

[Bob Phillips]

You forgot a closiing bracket

=IF(ISNA(VLOOKUP(TEXT(A15,"0"),Database!$A$1:$AN$420,5,0)),0,VLOOKUP(TEXT(A15,"0"),Database!$A$1:$AN$420,5,0))

See my other reply about the - as well, it is better than another IF test.

[jammer6_9]

Huhhh... Thanks a lot.


You forgot a closiing bracket

=IF(ISNA(VLOOKUP(TEXT(A15,"0"),Database!$A$1:$AN$420,5,0)),0,VLOOKUP(TEXT(A15,"0"),Database!$A$1:$AN$420,5,0))

See my other reply about the - as well, it is better than another IF test.

[zv735]

Another Method

1 select and change Format all Column A to number format
2 select all column A and uses texttocolumn click Finish

all data to change format to number

Thank you

[jammer6_9]

A finale of this formula, How can I make it in one statement.

This is to show #N/A to "0"
=IF(ISNA(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)),0,VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0))

This is to show "-" to "0"
=IF(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)="-",0,(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)))

Im getting #VALUE! with this
=IF(ISNA(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)),0,VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)),IF(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)="-",0,(VLOOKUP(TEXT(A17,"0"),Database!$A$1:$AN$420,11,0)))

[Bob Phillips]

See post #11 for a better way.

[jammer6_9]

Huhhh! I should pay attention to your codes.:o: Thanks once again...
SOLVED SOLVED SOLVED :whistle:


Tryt using SUM instead of manually adding

=IF(O16=0,0,SUM(G16,I16,K16))

instead of

=IF(O16=0,0,G16+I16+K16)