Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
Hi there,
I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
Provide some Addresses as Sample.
-------------------- (Ms-Exl-Learner) -------------------- "Mustang" wrote: Hi there, I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
Once you apply PROPER() function. Copy the cellsright
clickPasteSpecialValues and paste it to the same location to convert the formulas to values. Then select the cells Hit Ctrl+H to Find Replace....check Match Case Find 'Po ' (P0 followed by a space) Replace with 'PO ' (PO followed by a space) If PO is within the text then try find ' Po ' and replace with ' PO ' OR try the below formula =IF(ISNUMBER(FIND(" PO "," " & A1)),TRIM(REPLACE(" " & PROPER(A1), FIND(" Po "," " &PROPER(A1)),4," PO ")),PROPER(A1)) -- Jacob "Mustang" wrote: Hi there, I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
Brilliant, the formula works perfectly!
Many thanks "Jacob Skaria" wrote: Once you apply PROPER() function. Copy the cellsright clickPasteSpecialValues and paste it to the same location to convert the formulas to values. Then select the cells Hit Ctrl+H to Find Replace....check Match Case Find 'Po ' (P0 followed by a space) Replace with 'PO ' (PO followed by a space) If PO is within the text then try find ' Po ' and replace with ' PO ' OR try the below formula =IF(ISNUMBER(FIND(" PO "," " & A1)),TRIM(REPLACE(" " & PROPER(A1), FIND(" Po "," " &PROPER(A1)),4," PO ")),PROPER(A1)) -- Jacob "Mustang" wrote: Hi there, I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
On Wed, 16 Dec 2009 17:53:02 -0800, Mustang
wrote: Hi there, I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks How are you handling 2-letter state abbreviations? --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
Hi Ron,
We are NZ based so dont have the 2 letter state abbreviation. Thanks "Ron Rosenfeld" wrote: On Wed, 16 Dec 2009 17:53:02 -0800, Mustang wrote: Hi there, I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks How are you handling 2-letter state abbreviations? --ron . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with PROPER function
On Thu, 17 Dec 2009 16:28:01 -0800, Mustang
wrote: Hi Ron, We are NZ based so dont have the 2 letter state abbreviation. Thanks OK, if all you have to deal with is the Po issue, and if your PO is always preceded and followed by <space, then: =SUBSTITUTE(PROPER(A1)," Po "," PO ") should work. It works whether or not PO is in the original string. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PROPER FUNCTION | Excel Worksheet Functions | |||
PROPER function | Excel Worksheet Functions | |||
How do I use the PROPER function? | Excel Worksheet Functions | |||
PROPER function | Excel Worksheet Functions | |||
How do I use the PROPER function? | Excel Discussion (Misc queries) |