Sign up  |  Log in

Excel Help

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 :)

Don’t waste your valuable time with unreliable and bogus strategies for passing the CFA® exam. Schweser prepares you for success with adaptive tools, expert instruction, and proven study techniques personalized to your unique learning style.

I think is something like this:

new cell = LEFT(b3,LEN(b3)-2)

if you always have two characters @ the right.

----------
Contact me for Foreign Debt (Brazil, Argentina, Mexico) and/or Real Estate services
ptcrook78@gmail.com

Or you could use

=SUBSTITUTE(B3,”CR”,”“)

Soddy1979, CFA

Find+replace?

awesome! thanks guys :D

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.

Soddy1979, CFA

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

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.

Soddy1979, CFA

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

__________

"good personality ... or he was known as Lt. Mandingo during his army days."