PROPER Formula Meeting With Postcode Problems

Hi all,

I'm using the PROPER worksheet function to pull through some site names and address that are currently in capital letters. The formula I've got is working well with one little exception - Excel also changes the postcodes at the end of the addresses into PROPER case which is obviously wrong. For any brethren not in Britain, the UK postcode consists of one or two letters, a number, a space, a number and two letters - e.g. BS15 9PO. Excel turns this into Bs15 9Po.

Can anyone suggest a means to get Excel to ignore the postcode. If it helps, it is always seperated by a comma from the rest of the address. I've tried some FIND experiments with this in mind, but nothing is working.

The formula I'm using looks like this:

=PROPER(INDEX('\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$M$2533,MATCH(E12,'\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$A$2533,FALSE),4))

Can anyone suggest an ammendment/appendment to get the postcode to stay in its original capitalized format.

TIA,

SamuelT

=PROPER(LEFT(INDEX('\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$M$2533,MATCH(E12,'\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$A$2533,FALSE),4),LEN(INDEX('\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$M$2533,MATCH(E12,'\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$A$2533,FALSE),4)-8))&Right(INDEX('\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$M$2533,MATCH(E12,'\\ORACLE-B\Trackers\HUB Files\HUB\[HUB.xls]Hub'!$A$4:$A$2533,FALSE),4),8)

It's a bit lengthy with the Index/Match being used so often and referring to other workbooks, but if I didn't mess up parentheses placement, it should work.

Perhaps the easiest fix is to just pull the data into a helper column using Index/Match and then hiding it. Then the formulas NBVC and I provided could refer to just that cell, making the formula much more readable and also improving performance a little.

Hi guys,

Thanks for the assist. I think you're right - putting in a second column will simplify things. Just tried NBVC concentate method - working well.

Thanks,

SamuelT