functions

S

Sandy

I am trying to create a formulae to do the following:

postal code: L1T3H-7

should be

L1T 3H7

can anyone help me out.
 
C

Chip Pearson

Sandy,

Try the following formula:

=LEFT(SUBSTITUTE(A1,"-",""),3)&" "&RIGHT(SUBSTITUTE(A1,"-",""),3)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
E

Eric

Sandy,

Here's one way. I left your original text in cell A1 and
put the following formula in cell B1:

=LEFT(A1,3)&" "&SUBSTITUTE(RIGHT(A1,4),"-","")

HTH

Eric
 
G

Gerry Kuta

=SUBSTITUTE(A1,"-","")+0 to remove the - ...then you can
use
=REPLACE(A1,4,,"") to split it (within one cell)

Place these in the cells you need them to be in.

HRH
 
P

Peo Sjoblom

=SUBSTITUTE(SUBSTITUTE(A1,MID(A1,3,1),MID(A1,3,1)&" "),"-","")

With the post code in A1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top