# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi, I'm trying to have only numbers from one cell to another cell, ignoring letters. For example, if Cell A has "\$5.4k" I need it to write 5.4 in the other cell.
Solved by F. L. in 23 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/07/2018 - 05:53
Hi
Excelchat Expert 13/07/2018 - 05:53
Welcome to Got IT Pro.
User 13/07/2018 - 05:54
Hello!
Excelchat Expert 13/07/2018 - 05:54
Excelchat Expert 13/07/2018 - 05:54
You want number from the text?
User 13/07/2018 - 05:54
yes.
Excelchat Expert 13/07/2018 - 05:55
Ok. I am working on it. Please give me few minutes.
User 13/07/2018 - 05:55
I have a funciton already that works, but it doesn't work for decimals for some reason when there are letters/.
Excelchat Expert 13/07/2018 - 05:55
Excelchat Expert 13/07/2018 - 05:56
=value(mid(A1,2,len(A1)-2))
User 13/07/2018 - 05:57
with my function it seems to break when there are decimals and letters combined
User 13/07/2018 - 05:57
it works when there are only decimals or only letters. but not when there are both
Excelchat Expert 13/07/2018 - 05:57
User 13/07/2018 - 05:58
it seems to ignore decimal values
Excelchat Expert 13/07/2018 - 05:58
ok. You will not always have text at the end?
User 13/07/2018 - 05:58
usually I will.
User 13/07/2018 - 05:58
Let's assume that I will always have a lette at the end
Excelchat Expert 13/07/2018 - 06:01
5.3 can't be text. Is the format in text?
User 13/07/2018 - 06:02
under the format tab it says its automatic
User 13/07/2018 - 06:02
I can change it but the thing is I will be copying and pasting into the cell, so That will probably change the formatting every time
Excelchat Expert 13/07/2018 - 06:03
Yes. You have to use paste values only not with formatting.
Excelchat Expert 13/07/2018 - 06:11
=value(if(iserror(value(mid(A1,len(A1),1))),mid(A1,1,len(A1)-1),value(mid(A1,1,len(A1)))))
Excelchat Expert 13/07/2018 - 06:12
Excelchat Expert 13/07/2018 - 06:12
I am checking whether it is text or not at the end, if it is text, then skip the last character, else, get the complete text and convert to value.
User 13/07/2018 - 06:13
brilliant
User 13/07/2018 - 06:13
so is there any requirements as far as formatting?
Excelchat Expert 13/07/2018 - 06:13
It is one of the complicated formulas, i have experienced.
Excelchat Expert 13/07/2018 - 06:13
No need to worry about formatting.
User 13/07/2018 - 06:14
Does it only work when it is formatted as numbers
User 13/07/2018 - 06:14
ok great
Excelchat Expert 13/07/2018 - 06:14
As, I can see it is completely text.
User 13/07/2018 - 06:14
yes I've spent all day yesterday and this morning trying to figure it out
Excelchat Expert 13/07/2018 - 06:14
Please do visit Got IT Pro. Thanks for giving me an opportunity, to explore this formula.
Excelchat Expert 13/07/2018 - 06:14
Please do visit Got It Pro. We have 24 * 7 problem solving facility. You can visit any time.
Excelchat Expert 13/07/2018 - 06:15
Have wonderful day ahead. Please end the session and leave valuable feedback.
User 13/07/2018 - 06:15
is there any way to then detect the "k" in the cell and automatically multiply the number values by 1000?
Excelchat Expert 13/07/2018 - 06:16
Yes. there is possibility.
Excelchat Expert 13/07/2018 - 06:16
However, i would like to suggest to open a new session as it is going to be a new requirement. I have to work out the formula.
Excelchat Expert 13/07/2018 - 06:16
It is as per policies to answer one querey at a time.
User 13/07/2018 - 06:17
ok, i understand
User 13/07/2018 - 06:17
have a good day!
Excelchat Expert 13/07/2018 - 06:17
Have a great day ahead. Please do leave valuable feedback. Thank you. Looking forward.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.