Extracting text

[zack_]

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.

 

[Snakehips]

Not a lot less long winded than zack's original formula but does this cover most of the angles?

Excel 2007
	S	T	U	V	W	X	Y
8	1 years 1 months						1.1
9	10 years 10 months						10.10
10	0 years 11 months						0.11
11	10 months						0.10
12	12 years 1 months						12.1
13	4 months						0.4
Sheet1

Cell Formulas
Range	Formula
Y8	=IF(ISERROR(SEARCH(".", SUBSTITUTE(S8," years ","."))), "0."&SUBSTITUTE(S8," months",""), SUBSTITUTE(SUBSTITUTE(S8," years ",".")," months",""))

(It will fall down if the original time span is say '1 month' rather than 1 months')

 

[Scott Huish]

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:68px;" /><col style="width:68px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">1.01</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">8/12/2013</td><td style="text-align:right; ">9/25/2014</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4.11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">3/12/2009</td><td style="text-align:right; ">2/26/2014</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C8</td><td >=(DATEDIF<span style=' color:008000; '>(K8,L8,"y")</span>&"."&TEXT<span style=' color:008000; '>(DATEDIF<span style=' color:#0000ff; '>(K8,L8,"ym")</span>,"00")</span>)+0</td></tr><tr><td >C9</td><td >=(DATEDIF<span style=' color:008000; '>(K9,L9,"y")</span>&"."&TEXT<span style=' color:008000; '>(DATEDIF<span style=' color:#0000ff; '>(K9,L9,"ym")</span>,"00")</span>)+0</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[zack_]

Not a lot less long winded than zack's original formula but does this cover most of the angles?

Excel 2007

	S	T	U	V	W	X	Y
8	1 years 1 months						1.1
9	10 years 10 months						10.10
10	0 years 11 months						0.11
11	10 months						0.10
12	12 years 1 months						12.1
13	4 months						0.4

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

Sheet1​

Worksheet Formulas Cell Formula Y8 =IF(ISERROR(SEARCH(".", SUBSTITUTE(S8," years ","."))), "0."&SUBSTITUTE(S8," months",""), SUBSTITUTE(SUBSTITUTE(S8," years ",".")," months","")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

(It will fall down if the original time span is say '1 month' rather than 1 months')

This works perfectly -- however to Rick's original point -- 1.10 ( 1 year and 10 months ) and 1.1 (one year and one months ) does not make sense. Thinking through the solution -- the month portion of the formula is supposed to represent the fraction of a year that has elapsed. The reality is 1 year and ten months should really look like 1.83 (1 + 10 months/12 months).

I am trying to manipulate your formula to factor in dividing the month portion by 12 months. The True statement of the IF function is working, but I can't figure out how to calculate the months portion when joining year and month in the false statement. What I have so far:

=VALUE(IF(ISERROR(SEARCH(".", SUBSTITUTE(A7," years ","."))), "0"&(VALUE(SUBSTITUTE(A7," months",""))/12), SUBSTITUTE(SUBSTITUTE(A7," years ",".")," months","")))

 

[zack_]

Sorry, bad formula, try this one instead...

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

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","")

Thanks for your answer Rick. It works great -- though I have to confess I have a hard time following it even when I do the formula evaluator. I think I just need to work through it a couple times.

I am having a new issue and its something you alluded to ... that is 1.1 and 1.10 don't make sense. At least not from a computational stand point. Trying to subtract useful life in years to the number that I derive from the above formula doesn't give the right answer... the months portion should be a fractional representation of months/12 so that the unit I am working with is years. That way 1.83 years = 1 year and 10 months (10/12) rather than 1.10 which is what the formula currently gives.

 

[platonic567]

You can try my below formula:

=ROUND(IFERROR(0+LEFT(A1,SEARCH("years",A1)-2),0)+IFERROR(MID(A1,SEARCH("years",A1)+5,3)/12,LEFT(A1,SEARCH("months",A1)-2)/12),2)

It gives:
1.08 for 1 years 1 months
1.83 for 1 years 10 months
2 for 1 years 12 months

 

[Snakehips]

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

So you actually want years as a 'proper' decimal fraction rather than a 'representation' ?

In that case, the easiest way if you have them available, will be to use the start and finish dates directly with DATEDIF as suggested by Scott.

Assuming that you have start and finish dates in Q & R then try =DATEDIF(Q8,R8,"y")+(DATEDIF(Q8,R8,"ym")/12)

 

[zack_]

[/COLOR]

So you actually want years as a 'proper' decimal fraction rather than a 'representation' ?

In that case, the easiest way if you have them available, will be to use the start and finish dates directly with DATEDIF as suggested by Scott.

Assuming that you have start and finish dates in Q & R then try =DATEDIF(Q8,R8,"y")+(DATEDIF(Q8,R8,"ym")/12)

Thanks Everyone!!!!! That was a big time help --

I settled on this formula: which was basically the formula originally suggested by Scott but tweaked just to get the fomat I wanted.

=VALUE(TEXT(DATEDIF(O2,P2,"y")+(DATEDIF(O2,P2,"ym")/12),"#.000"))

I really appreciate all the help. I have only been using excel for about a year and this forum has been invaluable. Case in point, exercises like the above in which I struggle to understand everyone's suggestions but end up learning so much more than when I started.

So thanks again and keep up the good work!!

 

[Rick Rothstein]

I settled on this formula: which was basically the formula originally suggested by Scott but tweaked just to get the fomat I wanted.

=VALUE(TEXT(DATEDIF(O2,P2,"y")+(DATEDIF(O2,P2,"ym")/12),"#.000"))

Okay, but let's make sure this formula actually works the way you want. I don't know your data nor how exactly you wanted to calculate months so they can be used in other calculations, so I will just present a possible problem for you to investigate. Check the answers the formula gives you for the following dates and see if they are what you expect...

Group 1
--------------------
January 1, 2014 to March 30, 2013
January 30, 2014 to March 30, 2013
January 31, 2014 to March 30, 2013

Group 2
--------------------
April 30, 2014 to March 1, 2015
May 1, 2014 to March 1, 2015

 

[zack_]

Okay, but let's make sure this formula actually works the way you want. I don't know your data nor how exactly you wanted to calculate months so they can be used in other calculations, so I will just present a possible problem for you to investigate. Check the answers the formula gives you for the following dates and see if they are what you expect...

Group 1
--------------------
January 1, 2014 to March 30, 2013
January 30, 2014 to March 30, 2013
January 31, 2014 to March 30, 2013

Group 2
--------------------
April 30, 2014 to March 1, 2015
May 1, 2014 to March 1, 2015

Hi Rick,

Thanks for taking the time to think this through... You are right, my formula is yielding unexpected results...

1/1/2014	3/30/2014		0.167
1/30/2014	3/30/2014		0.167
1/31/2014	3/30/2014		0.083
4/30/2014	3/1/2015		0.833
5/1/2014	3/1/2015		0.833

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

I would expect that the January dates would all be .167, and I would also expect that the dates in April and May would differ.

I assume this has something to do with how the formula is counting the days... but that is just a guess, and something I need to research a little more. Below is an example from my spreadsheet and what I am trying to achieve

Date Installed Date Disposed Used Life Used Life (Number) Useful Life Fully Depreciated
11/11/2009 12/14/2010 1 years 1 months 1.08 7 No


The formula to get Used Life:
=IF(DATEDIF($O47,$P47,"y") = 0, DATEDIF($O47,$P47,"ym")& " months",DATEDIF($O47,$P47,"y")& " years "&DATEDIF($O47,$P47,"ym")& " months ")

Used Life:
=VALUE(TEXT(DATEDIF(O47,P47,"y")+(DATEDIF(O47,P47,"ym")/12),"#.000"))

Basically I am trying to display the data for used life so that its easy to interpret for anyone that will see the spread sheet. I converted used life from text to a number so that it could be used in calculations (I haven't got actual cost data yet for the servers, so which is why I haven't calculated depreciation)

 

[zack_]

Hi Rick,

Thanks for taking the time to think this through... You are right, my formula is yielding unexpected results...

1/1/2014	3/30/2014		0.167
1/30/2014	3/30/2014		0.167
1/31/2014	3/30/2014		0.083
4/30/2014	3/1/2015		0.833
5/1/2014	3/1/2015		0.833

<tbody>
</tbody>

I would expect that the January dates would all be .167, and I would also expect that the dates in April and May would differ.

I assume this has something to do with how the formula is counting the days... but that is just a guess, and something I need to research a little more. Below is an example from my spreadsheet and what I am trying to achieve

Date Installed Date Disposed Used Life Used Life (Number) Useful Life Fully Depreciated
11/11/2009 12/14/2010 1 years 1 months 1.08 7 No


The formula to get Used Life:
=IF(DATEDIF($O47,$P47,"y") = 0, DATEDIF($O47,$P47,"ym")& " months",DATEDIF($O47,$P47,"y")& " years "&DATEDIF($O47,$P47,"ym")& " months ")

Used Life:
=VALUE(TEXT(DATEDIF(O47,P47,"y")+(DATEDIF(O47,P47,"ym")/12),"#.000"))

Basically I am trying to display the data for used life so that its easy to interpret for anyone that will see the spread sheet. I converted used life from text to a number so that it could be used in calculations (I haven't got actual cost data yet for the servers, so which is why I haven't calculated depreciation)

Now that you have brought up this point, I see that my other formula would also be flawed since it is also based on the dated if function...

1/1/2014	3/30/2014		2 months
1/31/2014	3/30/2014		1 months

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

=IF(DATEDIF(B15,C15,"y") = 0, DATEDIF(B15,C15,"ym")& " months",DATEDIF(B15,C15,"y")& " years "&DATEDIF(B15,C15,"ym")& " months ")

I am currently researching date functions to see how to avoid this sort of error -- i may just wind up specifying that months are based on 30 day calendar days since that is typically how depreciation is calculated and I believe how we are calculating it on the program.

Though I still want to figure a solution to work around this just to see if I can... but I am glad you brought this point up to me so it is something that I can be cognizant of going forward.