Extracting text

zack_

Board Regular
Joined
Apr 18, 2014
Messages
79
Hi Everyone,

It took me awhile but I solved my issue. That said I wanted to know if there is a less convoluted formula to achieve the same result... I am trying to extract the number portion of a text string in order to convert it from text to a number which I will use to compare to the total useful life of that asset.

I used the datedif() function to figure out the difference between two dates and the results of the formula were returned in the below format:

1 years 4 months
1 years 4 months
1 years 5 months
10 years 6 months

<colgroup><col></colgroup><tbody>
</tbody>

I recognized that the years would always be either the first or second value and the months would always be either the ninth or tenth value depending on how many years there were.

To extract values I wanted I used the below formula:

=VALUE(IF(MID(S8,3,5) = "years",LEFT(S8,1)& "." &MID(S8,9,2),IF(MID(S8,4,5) = "years",(LEFT(S8,2)& "." &MID(S8,10,2)),TRIM("."& MID(LEFT(S8,9),1,2)))))

Is there a shorter formula I could use to achieve the same result? Thanks for the input.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Why not just use the DATEDIF formula again to return the format you want?

=(DATEDIF(K8,L8,"y")&"."&DATEDIF(K8,L8,"ym"))+0
 
Upvote 0
With your formula, how do you tell the difference between "1 years 1 months" and "1 years 10 months"?
 
Upvote 0
zack_,

Your formula is creating a 'faux decimal' number but assuming that is what you want then you could use ....

Excel 2007
QRSTUVWXY
812/08/201325/09/20141 years 1 months1.1
913/03/200926/02/20144 years 11 months4.11
Sheet1
Cell Formulas
RangeFormula
Y8=0+SUBSTITUTE(SUBSTITUTE(S8," years ",".")," months","")


Hope that helps.
 
Upvote 0
zack_,

Your formula is creating a 'faux decimal' number but assuming that is what you want then you could use ....
Excel 2007
QRSTUVWXY
812/08/201325/09/20141 years 1 months1.1
913/03/200926/02/20144 years 11 months4.11

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
Y8=0+SUBSTITUTE(SUBSTITUTE(S8," years ",".")," months","")

<tbody>
</tbody>

<tbody>
</tbody>
I wouldn't use the 0+ part of your formula, otherwise 1 month and 10 months end up looking the same. I realize that is what the OP's formula does, but I don't think it can be what the OP really wants. And I am not sure outputting 1.1 for one month and 1.10 for ten months (what your formula outputs if the 0+ is omitted) is all that much more clearer. Perhaps this would make more sense...

=TRIM(LEFT(S8,2))&"."&TEXT(MID(S8,9,2),"00")
 
Upvote 0
With your formula, how do you tell the difference between "1 years 1 months" and "1 years 10 months"?

Hi Rick,

That is a great point and something I overlooked. I tested several iterations, but nothing that ended with 10 months. I am not sure how to differentiate between the two. But am going to try to figure it out.

Snakechips,

I did mean to create a faux decimal -- thank you for your solution I will try it out now!
 
Upvote 0
Also I don't think I made this clear in my original post -- there are times when the datedif formula returns things like below:

11 months
10 months
5 months

<colgroup><col></colgroup><tbody>
</tbody>

which is why I tried to use the if function to test to see if there were years first
 
Upvote 0
I wouldn't use the 0+ part of your formula, otherwise 1 month and 10 months end up looking the same. I realize that is what the OP's formula does, but I don't think it can be what the OP really wants. And I am not sure outputting 1.1 for one month and 1.10 for ten months (what your formula outputs if the 0+ is omitted) is all that much more clearer. Perhaps this would make more sense...

=TRIM(LEFT(S8,2))&"."&TEXT(MID(S8,9,2),"00")

Hi Rick,

Thanks for taking the time to answer my question... when I copy the formula over to my workbook I get the following for

10 years 10 months = 10.01
10 years 1 months = 10.01

The problem that you pointed out still exists...

The original intent of the format I chose was for the number to the left of the decimal to represent years and the number to the right to represent decimals. So 10.5 would be 10 years and 5 months.
 
Upvote 0
zack_,

Your formula is creating a 'faux decimal' number but assuming that is what you want then you could use ....
Excel 2007
QRSTUVWXY
812/08/201325/09/20141 years 1 months 1.1
913/03/200926/02/20144 years 11 months 4.11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
Y8=0+SUBSTITUTE(SUBSTITUTE(S8," years ",".")," months","")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Hope that helps.

I just tested your solution -- and as Rick pointed out "10 years 10 months" and "10 years 1 months" both were represented by 10.1.

I appreciate your help though, and apologize that my original formula had that error in it. Thanks for using the substitute formula -- its not something I am familiar with and plan to check it out, after I fix my formula.
 
Upvote 0
Hi Rick,

Thanks for taking the time to answer my question... when I copy the formula over to my workbook I get the following for

10 years 10 months = 10.01
10 years 1 months = 10.01

The problem that you pointed out still exists...
Sorry, bad formula, try this one instead...

=LEFT(S8,FIND(" ",S8)-1)&"."&TEXT(MID(S8,FIND(" ",S8,FIND(" ",S8)+1)+1,2),"00")


The original intent of the format I chose was for the number to the left of the decimal to represent years and the number to the right to represent decimals. So 10.5 would be 10 years and 5 months.
I understand what you wanted, it is just your formula made it impossible to tell if the original time was 1 month or 10 months. If you don't mind having (what I consider odd) .1 for one month and .10 for ten months, then use Snakehips formula without the 0+ part...

=SUBSTITUTE(SUBSTITUTE(S8," years ",".")," months","")
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top