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

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

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

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

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
.

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

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