Cannot format column

**quintain** · 09-01-2009, 08:59 AM

I am an infrequent user of '07 excel.
I have copied and pasted three columns of figures and cannot format 2 columns for currency, the 3rd column has accepted formating and shows £1,234.00 from 1234
I have 'constantly' cleared formating (and comments) from the 2 problem columns and tried to convert them to currency. The results are 1234.00 (note no £ or comma seperator)
I would welome any advise available on this matter please.

**NBVC** · 09-01-2009, 09:01 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**quintain** · 09-01-2009, 09:35 AM

Originally Posted by NBVC

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Thak you for your quick reply and advice.
Would it be suitable if I selected a part of my work book obviously including the problem columns and uploaded that.

**NBVC** · 09-01-2009, 09:41 AM

Yes, we only need to see what the problem is.

**quintain** · 09-01-2009, 12:59 PM

Originally Posted by NBVC

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I forward a small section of the file and hope you can advise me on how to format the 2 x columns for £ currency.
However i have now discoverd if I enter the same amount in any of the problem cells the currency symbol and thousands seperator is correct i.e. 10.08 overwritten becomes £10.08
How can I do this for the whole column without overwriting every seperate entry (its a long column for my tax returns)

**NBVC** · 09-01-2009, 01:15 PM

You have special character (Code 160) spaces after those numbers... which tells excel these are text strings and not numbers.

You need to replace those characters with nothing...

Go to the Home tab and then in the Editing section click down arrow on Find and Select option.. choose Replace.

Now in the Find What box, hold the ALT key down and enter 0160 using only the number pad on the right of your keyboard.

Leave the other field blank

Then click Replace All

**quintain** · 09-01-2009, 04:45 PM

That's excellent and thank you, I will carry out your procedure shortly BuT how do you or more correctly how do I see or find out about the special characters (You have special character (Code 160) spaces after those numbers... which tells excel these are text strings and not numbers) knowing how to see/identify the characters may help me in the future.

**NBVC** · 09-01-2009, 04:52 PM

if you click in one of those cells and then hit F2 to get to the end of the content, you will notice the cursor has a space before it... at first it looks like a regular hit of the space bar.. when I tried to replace it, it didn't work, so I knew something fishy was up... so I entered a simple formula, =Code(Right(A1,1)) which takes the right-most character and tells me what code it is... and it returned 160... that means the code for the space is 160 and is not a regular space.. I then replaced that using the method I described and adding a 0 in front, so 0160, and it worked.

**NBVC** · 09-01-2009, 09:28 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**quintain** · 09-02-2009, 07:41 AM

NBVC
Thank you for your help, it has solved my excel currency problem.

I would welcome your advice again on a simple pre-designed template for my UK annual tax return, if you have any knowledge of such?

I will also understand if your advice is to ask it in the more public part of the forum.

I have completed 'reputation & solved' elements.

Thank you again for being willing and able to assist me.