How can I find cells with **.00 in number?

[Bridget365]

I'm going to try not to be too confusing with what I'm needing. I have two sheets.

My first sheet has numbers:

829.75

1563.15

1291.00

306.12

On my second sheet I need the numbers to pull and format as a 8 digit number with no decimal. I used =REPT(0,9-LEN(Sheet1!$K27))&SUBSTITUTE(Sheet1!$K27,".","") which worked beautifully until I got to the numbers that had no cents ".00". I've tried

00082975

00156315

00001291 *Should be 00129100

00030612

I tried using an IF function and IF with RIGHT (which I'm not entirely sure I'm using correctly) but nothing is working. It is not finding the 00 at the end...I'm assuming because it's not really there. I can't change the number to general because I need it to show the .00 on Sheet 1.
=IF(RIGHT(Sheet1!$K8,2)="00",REPT(0,6-LEN(Sheet1!$K8))&SUBSTITUTE(Sheet1!$K8,".",""),"00"),REPT(0,9-LEN(Sheet1!$K8))&SUBSTITUTE(Sheet1!$K8,".",""))

Thanks in advance!!

 

[awoohaw]

are these numbers, or text numerals?

 

[Bridget365]

are these numbers, or text numerals?

They are numbers.

 

[awoohaw]

here are two ways, depending on your original data, and the result is a number. if you need text, then hold on, its coming...

mr excel questions 22.xlsm
	A	B	C	D	E
13	if starting as number			if starting as text
14		use nbr format 00000000			use nbr format 00000000
15	829.75	00082975		829.75	00082975
16	1563.15	00156315		1563.15	00156315
17	1291.00	00129100		1291.00	00129100
18	306.12	00030612		306.12	00030612
Bridget365

Cell Formulas
Range	Formula
B15:B18	B15	=A15*100
E15:E18	E15	=VALUE(D15)*100

 

[awoohaw]

here are 4 scenarios, starting with text or number and ending with text or number:

mr excel questions 22.xlsm
	A	B	C	D	E	F
13	if starting as number			if starting as text
14		use nbr format 00000000	text		use nbr format 00000000	text
15	829.75	00082975	00082975	829.75	00082975	00082975
16	1563.15	00156315	00156315	1563.15	00156315	00156315
17	1291.00	00129100	00129100	1291.00	00129100	00129100
18	306.12	00030612	00030612	306.12	00030612	00030612
Bridget365

Cell Formulas
Range	Formula
B15:B18	B15	=A15*100
C15:C18	C15	=TEXT(A15*100,"00000000")
E15:E18	E15	=VALUE(D15)*100
F15:F18	F15	=TEXT(VALUE(D15)*100,"00000000")

 

[jolivanes]

try

=TEXT(TRIM(CLEAN(A1*100)),"0000000")

and copy down as far as needed.

 

[Bridget365]

here are 4 scenarios, starting with text or number and ending with text or number:

mr excel questions 22.xlsm
	A	B	C	D	E	F
13	if starting as number			if starting as text
14		use nbr format 00000000	text		use nbr format 00000000	text
15	829.75	00082975	00082975	829.75	00082975	00082975
16	1563.15	00156315	00156315	1563.15	00156315	00156315
17	1291.00	00129100	00129100	1291.00	00129100	00129100
18	306.12	00030612	00030612	306.12	00030612	00030612
Bridget365

Cell Formulas
Range	Formula
B15:B18	B15	=A15*100
C15:C18	C15	=TEXT(A15*100,"00000000")
E15:E18	E15	=VALUE(D15)*100
F15:F18	F15	=TEXT(VALUE(D15)*100,"00000000")

I tried the formula in B15 and it worked beautifully! And it was so simple that I'm mad I didn't see it!

Thank you soo much!!

 

[awoohaw]

@Bridget365 , My pleasure. I'm happy you were able to find a solution.
And welcome to the Mr. Excel forum.

I actually quite enjoyed @jolivanes suggestion, as I have never seen the CLEAN function before. So I learned today as well!

Best Wishes!