excel - insert space automatically with formula

[JSH720]

I have a string that looks like this:
10N23w05
I need it to look like this 10N 23W 05

In other words, with a space where indicated. Further complicated by the fact that some of the 50000 instances already have a space. they don't need another space entered. Is there a custom format,or formula or something else that can be set up. I'm not allowed to use macros.

Thanks.

 

[Joe4]

Will all the entries EXACTLY follow the format of:
3 characters + 1 space + 3 characters + 1 space + 2 characters?

If so, try this formula (written for an entry in cell A1):

=IF(LEN(TRIM(A1))=8,LEFT(A1,3) & " " & MID(A1,4,3) & " " & MID(A1,7,2),A1)

 

[Marcelo Branco]

Will all the entries EXACTLY follow the format of:
3 characters + 1 space + 3 characters + 1 space + 2 characters?

Or...
=REPLACE(REPLACE(A1,4,," "),8,," ")

M.

 

[Joe4]

=REPLACE(REPLACE(A1,4,," "),8,," ")

Marcelo,
That doesn't quite seem to work for entries that already have spaces in them. It seems to double them up.

 

[Marcelo Branco]

Marcelo,
That doesn't quite seem to work for entries that already have spaces in them. It seems to double them up.

Oh, you're right

What about
=REPLACE(REPLACE(SUBSTITUTE(A1," ",""),4,," "),8,," ")

M.

 

[Joe4]

What about
=REPLACE(REPLACE(SUBSTITUTE(A1," ",""),4,," "),8,," ")

That seems to work correctly.

 

[Eric W]

If the numbers can have other than 2 digits, then maybe:

=TRIM(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"N","N "),"W","W "))

and if the N and W represent North and West, then this can handle S and E:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"N","N "),"S","S "),"E","E "),"W","W "))

 

[dave2018]

If the numbers can have other than 2 digits, then maybe:

=TRIM(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"N","N "),"W","W "))

and if the N and W represent North and West, then this can handle S and E:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"N","N "),"S","S "),"E","E "),"W","W "))

Will they ever come up with a SUBSTITUTE function that takes away the need to nest 4 substitutes? like having optional 2nd and 3rd arguments??

i just searched uservoice, and it has this suggestion, but i thought it would be more popular..
https://excel.uservoice.com/forums/...-substitute-text-old-text1-old-text2-new-text

 

[JSH720]

My apologies. This went to my spam folder and I just saw it. Yes they all will follow that exact format, it worked thank you! and may your new year and the years to follow be filled with fun and health!

 

[Joe4]

You are welcome.
Glad we were able to help!