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?
|