| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dissecting the contents of a cell
I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Hi Doug
On the extracted values use =--(RIGHT(A1)&LEFT(A1,LEN(A1)-1)) -- Regards Roger Govier "Doug" wrote in message ... I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Try this:
B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
A less complicated way:
B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Ron, what means the 1,255 at the end of the formula?
thanks Marcelo from Brazil "Ron Coderre" escreveu: A less complicated way: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Thanks Roger,much appreciated.
Doug "Roger Govier" wrote: Hi Doug On the extracted values use =--(RIGHT(A1)&LEFT(A1,LEN(A1)-1)) -- Regards Roger Govier "Doug" wrote in message ... I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Yes excellent,thanks a lot.
Doug "Ron Coderre" wrote: A less complicated way: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Marcelo:
Ron, what means the 1,255 at the end of the formula? B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) The MID function requires 3 arguments -Text to work with. -The char number within that Text to be the start of the substring to pull. -The number of characters to pull, beginning with that char number. In the above formula, the start character of the substring is the 1st character AFTER the colon (:). So, the middle function argument uses the SEARCH function finds the position of the colon (:) and adds 1 to that number. For the 3rd function argument, the number of characters to pull, I simply chose 255 as a number that would most likely be larger then the length of the string being evaluated. I could have consumed a little more processor time and ensured a large enough 3rd value by using LEN(A1), instead of 255 as in: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,LEN(A1))) Does that help? *********** Regards, Ron XL2002, WinXP "Marcelo" wrote: Ron, what means the 1,255 at the end of the formula? thanks Marcelo from Brazil "Ron Coderre" escreveu: A less complicated way: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Ron, thanks for the feedback
regards Marcelo "Ron Coderre" escreveu: Marcelo: Ron, what means the 1,255 at the end of the formula? B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) The MID function requires 3 arguments -Text to work with. -The char number within that Text to be the start of the substring to pull. -The number of characters to pull, beginning with that char number. In the above formula, the start character of the substring is the 1st character AFTER the colon (:). So, the middle function argument uses the SEARCH function finds the position of the colon (:) and adds 1 to that number. For the 3rd function argument, the number of characters to pull, I simply chose 255 as a number that would most likely be larger then the length of the string being evaluated. I could have consumed a little more processor time and ensured a large enough 3rd value by using LEN(A1), instead of 255 as in: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,LEN(A1))) Does that help? *********** Regards, Ron XL2002, WinXP "Marcelo" wrote: Ron, what means the 1,255 at the end of the formula? thanks Marcelo from Brazil "Ron Coderre" escreveu: A less complicated way: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
Ron,
I have tried your formula on a different cell value and I always come up with "#VALUE". In cell A1 I have a date and time value, looks something like this: 5/25/2006 12:53:58 AM. I'm trying to extract just the time (12:53:58 AM) and so far no luck; can you steer me in the right direction. Thanks "Ron Coderre" wrote: Marcelo: Ron, what means the 1,255 at the end of the formula? B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) The MID function requires 3 arguments -Text to work with. -The char number within that Text to be the start of the substring to pull. -The number of characters to pull, beginning with that char number. In the above formula, the start character of the substring is the 1st character AFTER the colon (:). So, the middle function argument uses the SEARCH function finds the position of the colon (:) and adds 1 to that number. For the 3rd function argument, the number of characters to pull, I simply chose 255 as a number that would most likely be larger then the length of the string being evaluated. I could have consumed a little more processor time and ensured a large enough 3rd value by using LEN(A1), instead of 255 as in: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,LEN(A1))) Does that help? *********** Regards, Ron XL2002, WinXP "Marcelo" wrote: Ron, what means the 1,255 at the end of the formula? thanks Marcelo from Brazil "Ron Coderre" escreveu: A less complicated way: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dissecting the contents of a cell
The value in A1 is probably an actual date/time, so you don't need to do any
string gymnastics to extract the time. Try this: A1: 5/25/2006 12:53:58 AM B1: =A1 Format B1 as Time OR B1: =MOD(A1,1) Again, just format B1 as Time Does that help? *********** Regards, Ron XL2002, WinXP "El Bee" wrote: Ron, I have tried your formula on a different cell value and I always come up with "#VALUE". In cell A1 I have a date and time value, looks something like this: 5/25/2006 12:53:58 AM. I'm trying to extract just the time (12:53:58 AM) and so far no luck; can you steer me in the right direction. Thanks "Ron Coderre" wrote: Marcelo: Ron, what means the 1,255 at the end of the formula? B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) The MID function requires 3 arguments -Text to work with. -The char number within that Text to be the start of the substring to pull. -The number of characters to pull, beginning with that char number. In the above formula, the start character of the substring is the 1st character AFTER the colon (:). So, the middle function argument uses the SEARCH function finds the position of the colon (:) and adds 1 to that number. For the 3rd function argument, the number of characters to pull, I simply chose 255 as a number that would most likely be larger then the length of the string being evaluated. I could have consumed a little more processor time and ensured a large enough 3rd value by using LEN(A1), instead of 255 as in: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,LEN(A1))) Does that help? *********** Regards, Ron XL2002, WinXP "Marcelo" wrote: Ron, what means the 1,255 at the end of the formula? thanks Marcelo from Brazil "Ron Coderre" escreveu: A less complicated way: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),SEARCH(":",A1)+1,255)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: B1: =--(RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1 )-1)&"0123456789")),99)) Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP "Doug" wrote: I am trying to take out the number value from cells A1 to A3 and return them in a value that i can sum in B4.i can take the text out by using the formula below,my problem is that the + and - figures appear at the end of the number and so cannot be "sumed".Can they be returned in front of the number or return as a +or- figure so i can sum them? Any help appreciated. A B 1 Tot Val: 5.70- 5.70- 2 Tot Val: 6.64+ 6.64+ 3 Tot Val: .00+ .00+ =SUBSTITUTE(A1,"Tot Val:","") Doug M |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| separating numbers and letters from alphanumeric cell contents | Excel Worksheet Functions | |||
| Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
| Function syntax to compare cell contents | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
