1

I have a worksheet where I am getting direct output from STATA that has a weird character format that included " =[ number ]". I put in a new column to strip the characters and leave the numbers. However, the new cell is not recognized as a number, even thought the formatting is turned on to currency. I have to manually click in the cell and then hit enter to get it to recognize as a number.

A            B                        C
="[-7.734    =RIGHT(A1,LEN(A1)-3)     -7.734

But When I go to plot values in Col C, it is not working.

A) Why is this happening? B) Is there a fix for all cells?

3 Answers 3

3

You could try wrapping your text-trimming formula in VALUE(...) to convert the string result to a number.

For example:

=VALUE(RIGHT(A1,LEN(A1)-3))
1
  • Ah--so simple, but so perfect. Thanks!
    – jebediah
    Sep 26, 2014 at 15:16
2

Some more alternatives:

=RIGHT(A1,LEN(A1)-3)+0

=RIGHT(A1,LEN(A1)-3)*1

=--RIGHT(A1,LEN(A1)-3)

=SUM(RIGHT(A1,LEN(A1)-3))

EDIT

Just to add a bit of explanation...

All of these things work in a similar way. As standard, the RIGHT function will return as a text string. However, if possible, Excel will implicitly convert a text string to a number in order to perform a mathematical operation on it. Therefore, the problem is solved if we perform a mathematical operation which will not change the result.

As shown, this can be done in a number of ways:

Adding zero.
Multiplying by one.
Changing to a negative value and back again.
Summing the single value.
3
  • This is a good answer; it would be even better with just a couple of quick words explaining how each possibility works to functionally convert the data to a numeric value.
    – Alex M
    Sep 26, 2019 at 18:15
  • @AlexM Fair comment. I was a bit rushed when I wrote this. I will add some explanation when I have time.
    – Gravitate
    Sep 26, 2019 at 18:19
  • It's pretty much implicit, and I upvoted anyway, I just personally tend to prefer verbose answers ;)
    – Alex M
    Sep 26, 2019 at 18:19
1

You can easily do that by "Text to Column" conversion.

  1. Select the column and click Data > Text to Column
  2. Select Delimiter and then Next
  3. Uncheck all the delimiter patterns and click Finish.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .