Excel Help

[archived_user]

If I have a large series of numbers but each number has a “CR” after it in the cell, is there anyway I can eliminate the letters for a series of 1000+ numbers without doing it manually?
for example: cell b3 = “3,405.73CR”
thanks

 

[archived_user]

I think is something like this:
new cell = LEFT(b3,LEN(b3)-2)
if you always have two characters @ the right.

 

[archived_user]

Or you could use
=SUBSTITUTE(B3,”CR”,”“)

 

[archived_user]

Find+replace?

 

[archived_user]

awesome! thanks guys

 

[archived_user]

Serviced By Others Wrote:
——————————————————-
> Find+replace?
This one is the best. It automatically changes the text format of the cell with 10000CR to number format with 10000.
You won’t be able to sum or perform calculations on the new figures using either the SUBSTITUTE or LEFT functions, because they keep the text format and don’t recognise it as a number, even when you try to change the format using the menu bar.

 

[archived_user]

^^ i seem to be able to perform calculations with the number i have after using the LEFT function…

 

[archived_user]

Really? I wasn’t able to when I tried it - was just getting zero when summing the new column. Anyway, all’s well that ends well.

 

[archived_user]

You could use the left function, copy and paste as values and then carry on as normal… A little too involved though.