formula or vba to remove dot, trailing spaces and format numbers to eight digits

[RAM1972]

Hi All

I have an issue with stastistcs id, looking formula or vba to make number to 8 digits as below.

However, after four digits(always) there are trailing space of 1 or 2 or 1 . spaces
the digits will be always 8 numbers , no alphabetical words.

There are eratic blank rows of 1 or 2 or 3 or 4 or more . but should not alter the blank rows
the list goes to 20000 rows of data

Any assistance would be grateful
Sample

4016.991
4016 .991
4016 991
4016 991
4016

should read 4016991

Data header 1

ht code	Expected results
4016.93	40169300
4016.94	40169400
4016.95	40169500
4016 991	40169900
4016 999	40170000
4017.00	40170000
4101 .2	41012000
4101.50	41015000
4101.90	41019000
4017.	40170000

<tbody>
</tbody>

 

[63falcondude]

How's this?

Excel 2010
	A	B
1	ht code	result
2	4016.93	40169300
3	4016.94	40169400
4	4016.95	40169500
5	4016991	40169910
6	4016999	40169990
7	4017.00	40170000
8	4101 .2	41012000
9	4101.50	41015000
10	4101.90	41019000
11	4017.	40170000
Sheet5

Cell Formulas
Range	Formula
B2	=SUBSTITUTE(SUBSTITUTE(A2,".","")," ","")&REPT(0,8-LEN(SUBSTITUTE(SUBSTITUTE(A2,".","")," ","")))

 

[RAM1972]

How's this?

Excel 2010
	A	B
1	ht code	result
2	4016.93	40169300
3	4016.94	40169400
4	4016.95	40169500
5	4016991	40169910
6	4016999	40169990
7	4017.00	40170000
8	4101 .2	41012000
9	4101.50	41015000
10	4101.90	41019000
11	4017.	40170000
Sheet5

Cell Formulas
Range	Formula
B2	=SUBSTITUTE(SUBSTITUTE(A2,".","")," ","")&REPT(0,8-LEN(SUBSTITUTE(SUBSTITUTE(A2,".","")," ","")))

Thanks working as a charn

 

[63falcondude]

Great! Glad to help.