Add leading zero to number in text string 1

[chasethewind]

I'm looking for an easy solution to add a leading zero to a text string in excel 2010.

There is the text string:

N5^43'W
it should say
N05^43'W

or

S6^7'E
should say
S06^07'E

Thanks

 

[mintjulep]

Just use find and replace.

Find "N", replace with "N0"
Find "S", replace with"S0

 

[chasethewind]

That wouldn't return the correct value on some cases.

I would end up with "N032^45'E
on
N32^45'E

 

[SkipVought]

hi,

Just text formulas like FIND, LEN, MID. Nothing magic.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

And I was supposed to know that there are cases different from your examples how?

 

[judgeh59]

this may not be easy but it seems to work on the data set you created and some corner cases I added...

=CONCATENATE(LEFT(E3,1),TEXT(MID(E3,2,FIND("^",E3,1)-2),"00"),"^",TEXT(MID(E3,FIND("^",E3)+1,FIND("'",E3)-FIND("^",E3)-1),"00"),"'",RIGHT(E3,1))

this assumes your data is in column E and the formula I put in column G

Hope this helps

Ernest

Be Alert, America needs more lerts

 

[chasethewind]

Thanks Judgeh59, that was exactly what I was after.

Solved

 

[SkipVought]

hey chase,

Over the past 8 years you have posted many questions and received many helpful replies from Tek-Tip members. Yet, you have NEVER posted a customary "THANK YOU" for these valuable posts.

Since the solution posted by "Judgeh59, that was exactly what I was after," it would be most fitting to...

[blue]
Thank judgeh59
for this valuable post![/blue]

These [purple]Little Purple Stars[/purple] identify threads for Tek-Tips browsers, as ones that have been helpful. They also act as a token "Thank You" to the one or ones who posted valuable information. And they identify the recipient as a grateful member.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[judgeh59]

you're welcome Sir

Ernest

Be Alert, America needs more lerts

 

[Andrzejek]

Skip,
It is pretty bad if you have to ‘squeeze’ some kind of token of appreciation for the help provided over the years. Pretty sad…
:-(

Have fun.

---- Andy