Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Function Question #2
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" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Function Question #2
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 "Krista" wrote: 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" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Function Question #2
One way:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2, ".0", " R",2) ".0", "T"), "0", ""), ".21", "") In article , Krista wrote: 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" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Function Question #2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,". 0","T",1),"0",""),".","
R",1),".21","") "Krista" wrote: 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" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Function Question #2
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(F 2,".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 "Krista" wrote in message ... 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" |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Substitute Function Question #2
Thank you soooo much. That makes perfect sense. Thanks for explaining it!
"Elkar" wrote: 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 "Krista" wrote: 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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUBSTITUTE Function - Nesting Limitation | Excel Worksheet Functions | |||
Substitute function | Excel Discussion (Misc queries) | |||
similar function for SUBSTITUTE in excel? | Excel Discussion (Misc queries) | |||
Case function substitute | Excel Discussion (Misc queries) | |||
Replace or Substitute for COMBIN function | Excel Discussion (Misc queries) |