Removing a period and leaving maintaingn a fixed #of numbers

[Guest]

Good Morning,

How Can I combine these two?

=substitute(a1,".","") and =left(a1,9)

I have a string of numbers and I have a period among those numbers(the
period is not in a fixed place), I would like to remove the period and always
keep first left nine digits.

Lets say 42.254420115600000000, 422542011

Thank you for your help

 

[Ron Coderre]

Try this:

=LEFT(SUBSTITUTE(A10,".",""),9)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Thank you

Try this:

=LEFT(SUBSTITUTE(A10,".",""),9)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Hi,

One more question
Cell is empty
#VALUE!
Callable 12/15/07@101 (should be blank)
Callable - Sinking Fund 05/01/08@100 - Escrowed Escrowed
Callable 05/15/08@100 - Bank
Escrowed

=TRIM(MID(CallDate,FIND("@",CallDate)+6,LEN(CallDate)))

Thank you for help

How do I get above results without the #value!

 

[Ron Coderre]

Try this:

=IF(COUNTIF(A10,"*@????-*"),TRIM(MID(A10,FIND("@",A10)+6,LEN(A10))),"")
Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Thank You

Try this:

=IF(COUNTIF(A10,"*@????-*"),TRIM(MID(A10,FIND("@",A10)+6,LEN(A10))),"")
Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)