| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing a period and leaving maintaingn a fixed #of numbers
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing a period and leaving maintaingn a fixed #of numbers
Try this:
=LEFT(SUBSTITUTE(A10,".",""),9) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Iguss" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing a period and leaving maintaingn a fixed #of numbers
Thank you
"Ron Coderre" wrote: Try this: =LEFT(SUBSTITUTE(A10,".",""),9) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Iguss" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing a period and leaving maintaingn a fixed #of numbers
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(CallDa te))) Thank you for help How do I get above results without the #value! "Ron Coderre" wrote: Try this: =LEFT(SUBSTITUTE(A10,".",""),9) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Iguss" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing a period and leaving maintaingn a fixed #of numbers
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) "Iguss" wrote in message ... 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(CallDa te))) Thank you for help How do I get above results without the #value! "Ron Coderre" wrote: Try this: =LEFT(SUBSTITUTE(A10,".",""),9) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Iguss" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing a period and leaving maintaingn a fixed #of numbers
Thank You
"Ron Coderre" wrote: 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) "Iguss" wrote in message ... 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(CallDa te))) Thank you for help How do I get above results without the #value! "Ron Coderre" wrote: Try this: =LEFT(SUBSTITUTE(A10,".",""),9) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Iguss" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete all negative numbers leaving only positive numbers | Excel Discussion (Misc queries) | |||
| Determine payback period to reach fixed dollar amount. | Excel Worksheet Functions | |||
| drawdown at fixed rate over set period from investment at fixed % | New Users to Excel | |||
| question about removing text from cells, leaving numbers | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Hybrid Mode
