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

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

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

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

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
.

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

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