98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » After using 'right' formula, excel doesn't recognise the numbers
After using 'right' formula, excel doesn't recognise the numbers
Resolved · Urgent Priority · Version 2007
Mon 28 May 2012, 11:40Delegate Omar said...
Omar has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
After using 'right' formula, excel doesn't recognise the numbers
Morning,
After using the 'Right' formula to obtain some figures, excel doesn't seem to recongnise them as numbers. I have tried changing the format to number, paste them as values and also pasting them and then clicking on teh small sign on the left and press 'convert to number', but nothing seems to work.
Strangely enough, I have no problem converting into numbers the values I obtained with the 'left' and 'mid' formula. It is only with those obtained with the 'right' formula.
Is there any other way I can make excel recognise these values as numbers?
I am currently entering a large amount of data and a prompt answer would be most helpful.
Many thanks in advance
Omar
Mon 28 May 2012, 14:41Delegate Eileen said...
RE: after using 'right' formula, excel doesn't recognise the num
Hi Omar,
If the data in cell A1 is 'test 22'
Enter formula in B2 and this should bring up the number 22
=VALUE(RIGHT(A1, 2))
See if you can adapt this for your formula.
Kind Regards,
Eileen.
Mon 28 May 2012, 14:58Delegate Omar said...
RE: after using 'right' formula, excel doesn't recognise the num
thanks Eileen, but unfortunately it doesn't seem to work.
It actually yields the #value error.
any other suggestion? is there any way I can attach a file with a sample fo the data for you to have a go?
Omar
Mon 28 May 2012, 15:13Delegate Eileen said...
RE: after using 'right' formula, excel doesn't recognise the num
Hi Omar,
What is the data you have in your cell and what do you want to strip out?
If you key in your example in cell a1 and what you want to appear in cell b1 and I can have a look?
Kind Regards,
Eileen.
Mon 28 May 2012, 15:30Delegate Omar said...
RE: after using 'right' formula, excel doesn't recognise the num
For example: 4.384.63 I want the two numbers i.e.4.38 and 4.63 to be in two different cells and be recognised as numbers. But that onyl happens when I use the 'left' command. With the 'right' command the value is not recognised as a number.
A friend has just suggested I use the left formula afterusing the right formula and it actually works, but surely there might be a tidier alternative?
Mon 28 May 2012, 15:47Delegate Eileen said...
RE: after using 'right' formula, excel doesn't recognise the num
Hi Omar,
I'm not sure why your example isn't working
4.384.63
=VALUE(LEFT(A1,4)) ans is 4.38
=VALUE(RIGHT(A1,4)) ans is 4.63
is the cell always in the format x.xxx.xx? or will the numbers increase? Coul you do a text to columns between the 4.38 and 4.63?
Mon 28 May 2012, 16:05Delegate Omar said...
RE: after using 'right' formula, excel doesn't recognise the num
The problem is not that it doesn't show the value. The problem is that excel does not recognise it as a number and therefore I can't use functions with the data. For example when I try multpiplying the value obtained with the 'right' formula I get #VALUE
Anyway, thanks for your help. I am not conversant with the text to column function but I will try it.
Wed 30 May 2012, 13:22Trainer Mark said...
RE: after using 'right' formula, excel doesn't recognise the num
Hello Omar,
The formula given previous will work without the VALUE function, simply use either the LEFT or RIGHT function e.g.
=LEFT(A1,4)
Simply Format the cells or columns as Number format. I have tried this and works fine, and can be used in calculations
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adding a comment to a formula1. At the end of the formula, add a + (plus) sign. |
