Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding numbers with letters | Excel Discussion (Misc queries) | |||
Expanding numbers and letters in a cell | Excel Discussion (Misc queries) | |||
Deleting a space between a group of Numbers & Letters in a cell | New Users to Excel | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) | |||
FORMULAS FOR ADDING LETTERS REPRESENTING NUMBERS | Excel Discussion (Misc queries) |