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

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

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

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

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

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

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

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

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

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

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