Convert text to numbers

[Panoos64]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> Hi all, I download the below data from payroll system as shown in F.1. Kindly requesting to create a VB code so that to be able to sum the columns from “C” : “G”. Note that the rows are change each time when I download the report due to termination and new employments. As I realized the numbers appears as text and I would like to convert them to numbers so that to be able to sum them in col. "H". In F.2. I present the expected result. I would be greatly appreciated any support. Thanking you in advance



F.1.



[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 36"]
[/TD]
[TD="width: 156"] B
[/TD]
[TD="width: 83"] C
[/TD]
[TD="width: 67"]

D ​

[/TD]
[TD="width: 64"]

E ​

[/TD]
[TD="width: 70"]

F ​

[/TD]
[TD="width: 64"]

G ​

[/TD]
[TD="width: 63"]

H​

[/TD]
[/TR]
[TR]
[TD="width: 36"] 3
[/TD]
[TD="width: 156"] Employee Name
[/TD]
[TD="width: 83"] Balance B/F
[/TD]
[TD="width: 67"]

An.Leav ​

[/TD]
[TD="width: 64"]

P.H. ​

[/TD]
[TD="width: 70"]

Sundays ​

[/TD]
[TD="width: 64"]

Off ​

[/TD]
[TD="width: 63"]

Total​

[/TD]
[/TR]
[TR]
[TD="width: 36"] 4
[/TD]
[TD="width: 156"] Xxxxxxxxxx xxxxxxxx
[/TD]
[TD="width: 83"]
[/TD]
[TD="width: 67"]

02.04 ​

[/TD]
[TD="width: 64"]

00.00 ​

[/TD]
[TD="width: 70"]

00.00 ​

[/TD]
[TD="width: 64"]

08.62​

[/TD]
[TD="width: 63"]

0.00​

[/TD]
[/TR]
</tbody>[/TABLE]



F.2.

[TABLE="class: MsoNormalTable, width: 500"]
<tbody>[TR]
[TD="width: 65"] [TABLE="class: MsoTableGrid, width: 452"]
<tbody>[TR]
[TD="width: 36"]
[/TD]
[TD="width: 156"] B
[/TD]
[TD="width: 83"] C
[/TD]
[TD="width: 67"]

D​

[/TD]
[TD="width: 64"]

E​

[/TD]
[TD="width: 70"]

F​

[/TD]
[TD="width: 64"]

G​

[/TD]
[TD="width: 63"]

H​

[/TD]
[/TR]
[TR]
[TD="width: 36"] 3
[/TD]
[TD="width: 156"] Employee Name
[/TD]
[TD="width: 83"] Balance B/F
[/TD]
[TD="width: 67"]

An.Leav​

[/TD]
[TD="width: 64"]

P.H.​

[/TD]
[TD="width: 70"]

Sundays​

[/TD]
[TD="width: 64"]

Off ​

[/TD]
[TD="width: 63"]

Total​

[/TD]
[/TR]
[TR]
[TD="width: 36"] 4
[/TD]
[TD="width: 156"] Xxxxxxxxxx xxxxxxxx
[/TD]
[TD="width: 83"]
[/TD]
[TD="width: 67"]

2.04 ​

[/TD]
[TD="width: 64"]

0.00 ​

[/TD]
[TD="width: 70"]

0.00 ​

[/TD]
[TD="width: 64"]

8.62​

[/TD]
[TD="width: 63"]

10.66​

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="width: 202"]
[/TD]
[TD="width: 80"]
[/TD]
[TD="width: 64"]

​

[/TD]
[TD="width: 64"]

​

[/TD]
[TD="width: 64"]

​

[/TD]
[TD="width: 64"]

​

[/TD]
[TD="width: 64"]

​

[/TD]
[/TR]
</tbody>[/TABLE]
<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} table.MsoTableGrid {mso-style-name:"Table Grid"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-border-insideh:.5pt solid windowtext; mso-border-insidev:.5pt solid windowtext; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

[mole999]

in H4 =--(D4+E4+F4+G4) an ensure formatted as number

 

[Panoos64]

Hi mole, it does not work. Please be informed that i use office 2003 and i do not know that is disadvantage for your formula. However many thanks for your support.

 

[mole999]

it went into H4 ?
it was as typed ?
H4 is formatted as a number and not text?
I don't recall a double minus sign being an issue in 2003

 

[Panoos64]

Hi mole, I inserted new column "H" in order to issue the sum from C - G and so i formatted it as numbers. When i entered your formula it issued as a result #VALUE. However do not worry about my project. I found the solution by searching through a web site and i found a vba code. I appreciated what you done for me and your time spend for me. Is very kind of you. Thanks once again!

 

[Panoos64]

Finally i couldn't find the solution for my project. The code which i used it works only once. I am kindly requesting for new vba code so that to convert the text numbers to actual numbers. Thanking you in advance

 

[jason7579]

Hi, I had a similar problem before. If you highlight all of the text numbers you should see a yellow diamond with a ! inside it at the top left of your highlighted cells.
You can then click this and choose Convert to Number. You should now be able to use these numbers for your calculations.

Jason

 

[Panoos64]

Hi jason, there is no such point when i highlighted the cells. Thanks for your support. Any other suggestions i would be greatly appreciated.

 

[jason7579]

I have just performed a few tests on this and have discovered the following:

The point will not show up if you Select all, or highlight rows or columns. If you highlight the cells by clicking and dragging the mouse over the range it should appear. The only rule being that you need to be on one of the affected cells when you first press the left mouse button. I am not sure if it is possible to do this any other way.

 

[Panoos64]

No jason it doesn't appear. Thanks for your suggestion.