My feed
Premium

Please
or
to access all these features

AIBU?

Please help me with excel - converting text to number

10 replies

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.

OP posts:

Am I being unreasonable?

0 votes. Final results.

POLL
You are being unreasonable
0%
You are NOT being unreasonable
0%
JustAnotherDayWorkingAtHome · 08/10/2022 10:53

The cells don't have the green triangle option to convert easily to a number

OP posts:
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.

OP posts:
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!

Please create an account

To comment on this thread you need to create a Mumsnet account.