Converting text to numbers

smithy0705

New Member
Joined
Feb 20, 2019
Messages
8
Hi Guys

I have this date format as text and would like to convert it to just numbers:

This is what I have '3ys 7ms 15ds' and I would like to try and display it as a round number say 3.70 or 3.07 whichever works

TIA

 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Maybe

=REPLACE((SUBSTITUTE(A1,"ys ",".")),FIND("ms",A1)-2,99,"")+0
 

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
Or try these
To get 3.7
=LEFT(SUBSTITUTE(A1,"ys ","."),FIND("ms",A1)-3)/1
To get 3.07
=SUBSTITUTE(LEFT(A1,FIND("m",A1)-1),"ys ",".0")/1

<b>Unknown</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">3ys 7ms 15ds</td><td style="text-align: right;;">3.7</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">3ys 7ms 15ds</td><td style="text-align: right;;">3.07</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">Sheet3</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,431
Members
409,876
Latest member
Akash Yadav
Top