Extract portion of a cell

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

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?
 
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)
 
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.
 
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
 
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.
 
Back
Top