Substitute Function Question #2

[Guest]

Help! I'm soooo close.

My cell looks like this - .0470N.0180E.21

I want my cell to look like this - T47N R18E

It currently looks like this - T47N.18E

Here is my subsitute function:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(F2,".0","T",1),"0",""),".0","R",2),".21","")

I just need the second .0 to be replaced by a <space> "R"

 

[Guest]

The Substitute functions are evaluated from the inside out. Therefore, when
you get the the part of replacing the 2nd occurance of .0 with R, there is no
2nd occurance, since the first has already been replace. It also won't be
found since you've already removed all the zeros.

Try writting the formula like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".0","T",1),".0","
R",1),"0",""),".21","")

HTH,
Elkar

 

[JE McGimpsey]

One way:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2, ".0", " R",2) ".0",
"T"), "0", ""), ".21", "")

 

[Guest]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".0","T",1),"0",""),".","
R",1),".21","")

 

[PCLIVE]

I'm not sure what you're trying to accomplish here, but here is a formula
that will do the trick. However, it may only apply to the one given number
that you've stated.

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".0","T",1),".0","
R"),"0",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".0","T",1),".0","
R"),"0",""))-(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".0","T",1),".0","
R"),"0",""))-FIND(".",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,".0","T",1),".0","
R"),"0",""))+1))

If there are any parts of the original number that are always the same, let
us know and someone may be able to come up with the solution regardless of
the number ( as long as the contstants are true in the number).

Good luck.
Paul

 

[Guest]

Thank you soooo much. That makes perfect sense. Thanks for explaining it!