Adding a space inbetween letters and numbers in a cell
 

Is there a way of adding a space between letters and numbers in a cell to
seperate them but keep them in the same cell?

I am based in the UK and have an excel list of UK Postcodes that need
cleaning, as instead of having a space correctly inbetween them eg NG12 9NA
some (not all) are listed incorrectly with no space eg NG129NA.

For reference UK Postcodes can have either 3 or 4 digits (NG1 or NG12) in
the first part but only ever 3 digits (9NA) in the second part. I am using
Excel 2003 on Windows XP Professional.

Any help would be greatly appreciated.

Adding a space inbetween letters and numbers in a cell
 

In a separate cell you could add

=LEFT(A2,LEN(A2)-3)&" "&RIGHT(A2,3)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jazzman10" wrote in message
...
Is there a way of adding a space between letters and numbers in a cell to
seperate them but keep them in the same cell?

I am based in the UK and have an excel list of UK Postcodes that need
cleaning, as instead of having a space correctly inbetween them eg NG12
9NA
some (not all) are listed incorrectly with no space eg NG129NA.

For reference UK Postcodes can have either 3 or 4 digits (NG1 or NG12) in
the first part but only ever 3 digits (9NA) in the second part. I am
using
Excel 2003 on Windows XP Professional.

Any help would be greatly appreciated.

Adding a space inbetween letters and numbers in a cell
 

Thanks Bob, you've saved me a lot of time.

"Bob Phillips" wrote:

In a separate cell you could add

=LEFT(A2,LEN(A2)-3)&" "&RIGHT(A2,3)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jazzman10" wrote in message
...
Is there a way of adding a space between letters and numbers in a cell to
seperate them but keep them in the same cell?

I am based in the UK and have an excel list of UK Postcodes that need
cleaning, as instead of having a space correctly inbetween them eg NG12
9NA
some (not all) are listed incorrectly with no space eg NG129NA.

For reference UK Postcodes can have either 3 or 4 digits (NG1 or NG12) in
the first part but only ever 3 digits (9NA) in the second part. I am
using
Excel 2003 on Windows XP Professional.

Any help would be greatly appreciated.