Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract portion of a cell
I have a column that is set to text and I need to extract just the portion
that is ".####". This text has a decimal value within it that I need to extract. How can I find the ".####" and extract just that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract portion of a cell
If your number is in A1 (in text format) then enter this in B1
=RIGHT(A1,LEN(A1)-FIND(".",A1)) If you want the decimal too then =RIGHT(A1,LEN(A1)-FIND(".",A1)+1) "Secret Squirrel" wrote: I have a column that is set to text and I need to extract just the portion that is ".####". This text has a decimal value within it that I need to extract. How can I find the ".####" and extract just that? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract portion of a cell
One follow up question. I'm using this formula you gave me:
=RIGHT(A1,LEN(A1)-FIND(".",A1)+1) But what if there is a number to the left of the decimal? I thought all my numbers were to the right but I found some that have 1 decimal place on the left. How can I also get this value as well? But it's only in certain cells so if there is no number to the left of the decimal then I only want the numbers to the right of the decimal. "Sheeloo" wrote: If your number is in A1 (in text format) then enter this in B1 =RIGHT(A1,LEN(A1)-FIND(".",A1)) If you want the decimal too then =RIGHT(A1,LEN(A1)-FIND(".",A1)+1) "Secret Squirrel" wrote: I have a column that is set to text and I need to extract just the portion that is ".####". This text has a decimal value within it that I need to extract. How can I find the ".####" and extract just that? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract portion of a cell
If you want to extract numbers from the right of a string use
=RIGHT(A1,LEN(A1)+1-MIN(FIND({0;1;2;3;4;5;6;7;8;9;"."},A1&"0123456789. "))) Caution: This will search for first number or decimal point and extract reamining part of string so ab1.23ab will give you 1.23ab "Secret Squirrel" wrote: One follow up question. I'm using this formula you gave me: =RIGHT(A1,LEN(A1)-FIND(".",A1)+1) But what if there is a number to the left of the decimal? I thought all my numbers were to the right but I found some that have 1 decimal place on the left. How can I also get this value as well? But it's only in certain cells so if there is no number to the left of the decimal then I only want the numbers to the right of the decimal. "Sheeloo" wrote: If your number is in A1 (in text format) then enter this in B1 =RIGHT(A1,LEN(A1)-FIND(".",A1)) If you want the decimal too then =RIGHT(A1,LEN(A1)-FIND(".",A1)+1) "Secret Squirrel" wrote: I have a column that is set to text and I need to extract just the portion that is ".####". This text has a decimal value within it that I need to extract. How can I find the ".####" and extract just that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract portion of a cell
Hi,
If I understand your question, select the column of numbers, and choose Data, Text to columns, choose Delimited, click Next, choose Other and enter . (a period) and click Finish. -- Thanks, Shane Devenshire "Secret Squirrel" wrote: I have a column that is set to text and I need to extract just the portion that is ".####". This text has a decimal value within it that I need to extract. How can I find the ".####" and extract just that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use/extract only a portion of a field | New Users to Excel | |||
Extract a specific portion of text as new cell value | Excel Discussion (Misc queries) | |||
Extract portion of formula resident in a cell | Excel Worksheet Functions | |||
I need to search for then extract a specific portion of cell data... | Excel Worksheet Functions | |||
Extract Portion of field into other fields | Excel Worksheet Functions |