Prefixing entries in colum with word

[irishshopper]

I have a spreadsheet containing rows of addresses including a field for County.

The County field is blank for any address where the Post Town has same name as the County ie Armagh, Londonderry, Antrim etc.

How can I prefix the name of the County in each cell with either "Co. " or "County " but only do it in the cells that have an entry ie leave the blank ones blank ?

[AlexandraT]

You can use an IF with a ISBLANK.

=IF(ISBLANK(A1),"","Co. "&A1) or
=IF(ISBLANK(A1),"","County "&A1)

A1 is the cell with the county in it.

Alex

[JazzKult]

You can use a helper column with an IF statement (if not blank then "co." & cell value) and then copy-paste. Another way to tackle this would be vba.

[irishshopper]

That is great. If the original entries are in Column A and we put the new combined input in Column B how can I delete column A after the work is done. If it is delighted as it is then all the entries in Column B will show as Error.

Can you lock the combined results in Columb B and then Delete Column A ?

[Pete_UK]

I can't believe you would have the town in Column A of a list of addresses.

Another way would be to highlight the column with the blank counties in it, then press F5 (GoTo), then click Special, then select Blanks and OK. With your cursor in the first blank cell (as it should have positioned itself) you can begin to type in the formula:

="Co "&

and at that point you can click the cell next to it that you want to get the town name from. Then instead of <Enter>, you should hold down the CTRL key and then press <Enter> and then release Ctrl. You will find that all those blank cells will now be filled with the appropriate formula, so there is no change to the rest of your data.

Hope this helps.

Pete

[AlexandraT]

Pete_UK's suggestion is a very good one. But if you still want to go ahead with the formulas I suggested, after you apply the formula in column B, copy the entire column B and then paste as values over it. It is safe to delete column A afterwards.To paste as values, right click, and then click Values under 'Paste Options:'.

Alex

[irishshopper]

I was only using Column A as an example. The actual Spreadsheet has the County in Column E.

Will try what you suggest Pete.

Many thanks.

Trevor

[irishshopper]

Just reading your post in more detail. I think I have not explained properly what I want.

If the County field is BLANK I want it to stay BLANK.

If the County field is not blank I want it to prefix whatever is in tat cell with "Co."

So

Antrim changes to Co. Antrim
stays
Down changes to Co. Down

[Pete_UK]

You will need to use a blank column for this (let's say column M). Then in M1 you can have this formula:

=IF(E1="","","Co. "&E1)

Copy this down to the bottom of your data. Then you need to fix the values - select all the cells in column M, click the <copy> icon, then right-click over the selected cells and choose Paste Special from the drop-down, then click against Values and OK then press the <Esc> key. Then you can copy column M and paste it over the contents of column E, and then you can delete column M.

Hope this helps.

Pete