Substitute Function Question #2

K

Krista

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"
 
E

Elkar

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
 
J

JE McGimpsey

One way:

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

Mike

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

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
 
Top