If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used w/ma
I have this data that is pasted to my worksheet:
Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve |
#2
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be usedw/ma
Steve wrote:
I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve One way: =--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) |
#3
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used w/ma
One way...
Assuming the word "Days:" is *always* present. =--MID(A1,SEARCH("Days:",A1)+5,5) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve |
#4
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
I'm getting a #value!
A1 is where my general format data is, correct ? "Glenn" wrote: Steve wrote: I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve One way: =--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) . |
#5
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
I do believe the "Days" will always be present, and your formula works to
produce either the single #, tens, or hundreds, but when I use that result as a divisor, it still produces a #value! E.g. Formula result is 6(in J3).... e19/j3 = #value! "T. Valko" wrote: One way... Assuming the word "Days:" is *always* present. =--MID(A1,SEARCH("Days:",A1)+5,5) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve . |
#6
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
E.g. Formula result is 6(in J3).... e19/j3 = #value!
What's in E19? When a formula like that resturns a #VALUE! error it usually means one (or possibly both) cells referenced contain TEXT. Since we know that J3 contains numeric 6 then the problem has to be with cell E19. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I do believe the "Days" will always be present, and your formula works to produce either the single #, tens, or hundreds, but when I use that result as a divisor, it still produces a #value! E.g. Formula result is 6(in J3).... e19/j3 = #value! "T. Valko" wrote: One way... Assuming the word "Days:" is *always* present. =--MID(A1,SEARCH("Days:",A1)+5,5) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve . |
#7
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You
probably have trailing spaces in your data, which could cause the #Value! error. Try this instead: B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3) Steve wrote: I'm getting a #value! A1 is where my general format data is, correct ? "Glenn" wrote: Steve wrote: I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve One way: =--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) . |
#8
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
This produced :6
I changed the 3's to 2's and got the 6, but I don't think my fix will account for 2 or 3 digit numbers, will it ? Same as other, taking a number in cell E19, and dividing by the resulting 6, still produces a #value! "Glenn" wrote: Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You probably have trailing spaces in your data, which could cause the #Value! error. Try this instead: B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3) Steve wrote: I'm getting a #value! A1 is where my general format data is, correct ? "Glenn" wrote: Steve wrote: I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve One way: =--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) . . |
#9
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
E19 has a number in it. Formatted as a number.
Also, in playing around, I may have changed the formatting of J3. It is currently formatted as a number. Thanks, Steve "T. Valko" wrote: E.g. Formula result is 6(in J3).... e19/j3 = #value! What's in E19? When a formula like that resturns a #VALUE! error it usually means one (or possibly both) cells referenced contain TEXT. Since we know that J3 contains numeric 6 then the problem has to be with cell E19. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I do believe the "Days" will always be present, and your formula works to produce either the single #, tens, or hundreds, but when I use that result as a divisor, it still produces a #value! E.g. Formula result is 6(in J3).... e19/j3 = #value! "T. Valko" wrote: One way... Assuming the word "Days:" is *always* present. =--MID(A1,SEARCH("Days:",A1)+5,5) -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve . . |
#10
|
|||
|
|||
Convert variable #'s in a gen. format to a # that can be used
What EXACTLY is in A1 (or whatever cell it is that you are looking at)?
Maybe try this, which assumes you are just subtracting the first date from the second date to get "Days": =MID(A1,FIND(" to ",A1)+4,11)-MID(A1,FIND(" to ",A1)-10,11) As for your other problem, put this in a blank cell and tell us what the result is: =ISNUMBER(E19) Steve wrote: This produced :6 I changed the 3's to 2's and got the 6, but I don't think my fix will account for 2 or 3 digit numbers, will it ? Same as other, taking a number in cell E19, and dividing by the resulting 6, still produces a #value! "Glenn" wrote: Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You probably have trailing spaces in your data, which could cause the #Value! error. Try this instead: B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3) Steve wrote: I'm getting a #value! A1 is where my general format data is, correct ? "Glenn" wrote: Steve wrote: I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisable #. Thanks, Steve One way: =--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) . . |
Thread Tools | |
Display Modes | |
|
|