Converting text to values

  • Thread starter Thread starter JENNA
  • Start date Start date
J

JENNA

Hi all,

I have a column of entries that get sent to me each month... they look
something like this.

123
123CR
2344DR
2223
123333
123cr
131331
22122Cr
22122Dr

etc etc

All the entries that have a CR, DR etc on the end are to be used
within calculations with all the other numeric values, as they are
still a dollar amount.

Basically, I need to write some code that checks this column and
removes any extra text characters from the end of these particular
entries, and makes them a numerical value that can then be used in
calculations.

Thanks for any help - Jen
 
Hi Jen
a non VBA approach:
enter the following in B1
=IF(ISNUMBER(--RIGHT(A1,1)),--A1,--LEFT(A1,LEN(A1)-2))
in C1 enter
=IF(ISNUMBER(--RIGHT(A1,1)),"",RIGHT(A1,2))
copy both formulas down
 
Back
Top