VBA remove decimals

Panoos64 · Apr 15, 2019

Hi all, I would like to use a VBA code so that to clear the numbers’ decimals which contains space before comma e.g. 26 ,7 should be 26 Therefore that, the code should run through col. “C4” : “O” and rows down. Please see below an extract of original data and the expected result.

Thank you all in advance

Original data

	A	C	D	E	F	G	H	I	J	K	L	M	N
4	Payroll	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
5	Payroll	31	28 ,3	31	30	31	30	25 ,7	31	30	31	30	31
4	Payroll	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
5	Payroll	31	31	31	30	10 ,7	30	31	31	12 ,6	31	30	31

Expected result

	A	C	D	E	F	G	H	I	J	K	L	M	N
4	Payroll	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
5	Payroll	31	28	31	30	31	30	25	31	30	31	30	31
4	Payroll	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
5	Payroll	31	31	31	30	10	30	31	31	12	31	30	31

oldbrewer · Apr 15, 2019





jan	feb	mar	apr	may
27	26.3	25.7	99.777	34






I used find speace comma replace with decimal point
to change 26 ,3 and 99 ,777

so with one command your whole spreadsheet is cleaned

do you want 99.777 to become 99.8 ?

pgc01 · Apr 15, 2019

Hi

2 questions

1 - Is it really a space followed by a comma? From the table in your post it seems like after the integer part of the number you have newline comma.

2 - like oldbrewer said

oldbrewer said:
... do you want 99.777 to become 99.8 ?...

or, as the title in your post suggests, you want 99.777 to become 99 (simply remove the decimals)?

Rick Rothstein · Apr 15, 2019

Here is VBA code to do what you asked for...

Code:

[table="width: 500"]
[tr]
	[td]Sub DeleteSpaceCommaNumber()
  Range("C4:N" & Cells(Rows.Count, "A").End(xlUp).Row).Replace " ,*", "", xlPart, , , , False, False
End Sub[/td]
[/tr]
[/table]

Panoos64 · Apr 15, 2019

oldbrewer and pgc01,

- apologies, no spaces exists between integer and comma
- numbers like 99,777 or 26,3 should be 99 & 26 respectively. Simply remove decimals

Thank you both for your support. Hv a great day

Panoos64 · Apr 15, 2019

Thank you so much Rick, it works perfect and based to my data.
Just for information purposes i done a mistake in my first post and note that NO space exists between integer and comma. So i written off the space like, " ,*", into ",*", and it runs perfect.
Lot of thanks for your continued support. Hv a great lovely day

VBA remove decimals

Panoos64

Well-known Member

Excel Facts

oldbrewer

Well-known Member

pgc01

MrExcel MVP

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

VBA remove decimals

Panoos64

Well-known Member

Excel Facts

oldbrewer

Well-known Member

pgc01

MrExcel MVP

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock