AIBU?
Please help me with excel - converting text to number
JustAnotherDayWorkingAtHome · 08/10/2022 10:51
I have googled and can't seem to get anything to work. I have data I have downloaded into a CSV file and it has formatted numbers as text, the numbers are in the form £10.45 so have a £ sign. I just can't seem to convert them to numbers....have tried various things.
Am I being unreasonable?
AIBUYou have one vote. All votes are anonymous.
JustAnotherDayWorkingAtHome · 08/10/2022 10:53
The cells don't have the green triangle option to convert easily to a number
Theopossumwasmeantforme · 08/10/2022 10:53
There’s a drop down in the number bit of the ribbon.
chatw0o0 · 08/10/2022 10:54
I've had this before - you may have to manually remove the pound sign. Or do find and replace but keep the replace box empty.
bingobanjo · 08/10/2022 10:54
Can you just highlight them all and then along the top there should be a drop-down that will say general/text/number/currency? Change that to text.
You could also try copying it all, then right click and select paste special > paste as values?
chatw0o0 · 08/10/2022 10:55
assuming you've done everything else that you usually would - ie, change format of cells from currency to number, etc.
Fleetheart · 08/10/2022 10:55
can you save it as excel file xls rather than csv and then you should be able to format column?
bingobanjo · 08/10/2022 10:57
Ooh or if the £ is actually a character within it rather than just formatted AS numbers, could you do ctrl+f for find, choose find and replace, put £ as the find and leave the replace blank? That would remove every £.
JustAnotherDayWorkingAtHome · 08/10/2022 10:58
I have manged to sort it using this formula
=VALUE(RIGHT(C2, LEN(C2)-1))
No idea what this formula is but it has worked, found it buried in an article about this problem...
Thanks there was too much data to re-type it all and had tried all the usual options.
Schoolchoicesucks · 08/10/2022 11:00
Can also type a number 1 in empty cell. Copy the 1. Highlight all the cells with the £values in, right click and paste special, then multiply.
titchy · 08/10/2022 11:02
Schoolchoicesucks · 08/10/2022 11:00
Can also type a number 1 in empty cell. Copy the 1. Highlight all the cells with the £values in, right click and paste special, then multiply.
That's my go-to method as well!
To comment on this thread you need to create a Mumsnet account.