Dissecting the contents of a cell

[Guest]

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

 

[Roger Govier]

Hi Doug

On the extracted values use
=--(RIGHT(A1)&LEFT(A1,LEN(A1)-1))

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Ron, what means the 1,255 at the end of the formula?

thanks
Marcelo from Brazil



"Ron Coderre" escreveu:

 

[Guest]

Thanks Roger,much appreciated.

Doug

Hi Doug

On the extracted values use
=--(RIGHT(A1)&LEFT(A1,LEN(A1)-1))

 

[Guest]

Yes excellent,thanks a lot.

Doug

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

 

[Guest]

Marcelo:

RE: > 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

 

[Guest]

Ron, thanks for the feedback

regards
Marcelo

"Ron Coderre" escreveu:

 

[Guest]

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

 

[Guest]

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