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
I have referred your requriement.
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
Please share it.
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
Please check in B1 cell.
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
Please use the above formula.
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.
