How to Replace & Insert Characters (Substitute Costum Format)

[hunter204]

Good Day To All:

I have a problem regarding on how to substitute the Cell.

First I also want to to know how Insert using Replace Function or any Function as long as it will be inserted.

Second is I want to Substitute my Cell A1 containing "91625" but the costum format is "##-##-##" so what I wanted is the result will be 9º 16.25`N.


I tried this formula given to me to substitute the TEXT but the problem is it is only valid if the format use is the TEXT.
9-16-25
=TEXT(SUBSTITUTE(SUBSTITUTE(E35,"-",":",1),"-","."),"[m]º s.00")&"`N"
Result: 9º 16.25`N

I'm using a Custom Format

 

[jmthompson]

A custom format doesn't actually put the hyphens in the cell, only displays them. If your numbers are always 5 digits with the digits split as outlined in your sample, you can use the following formula

=CONCATENATE(LEFT(A1,1),"º ",MID(A1,2,2),".",RIGHT(A1,2),"'N")