Removing text within a cell

[jonkl02]

Hi all,

I need some help with this. I need to extract numbers from a string of text, all within a cell. A sample of the text in as below:

Level: 9
Shuttle: 1
Estimated VO2max: 43.6 ml/kg/min

I would require the numbers to be extracted into 3 cells in different columns with the headers:
1) level
2) shuttle
3) estimated VO2max

Thank you!!

 

[kidwispa]

Use "Text to Columns" and choose Delimited Width. Then select ":" symbol under other on the next page

 

[jonkl02]

Hi kidwispa,

thanks for the prompt reply. However I need to extract all the numbers from the string below

e.g. in Cell A1
Level: 9
Shuttle: 1
Estimated VO2max: 43.6 ml/kg/min (these 3 rows are all typed into 1 cell)

I require the numbers separated into 3 columns as below:
Cell B1 :9
cell C1: 1
Cell D1: 43.6

appreciate the help. thank you!

 

[kidwispa]

Apologies for the misunderstanding... I think you may need some code in order to achieve this so think someone else would be better off helping you...

 

[Peter_SSs]

I've assumed that there is always 3 numbers. Try this User-Defined Function (UDF). To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy across the 3 columns and down as shown in B2:D4.

If you want to extract just a particular number (eg the second number following a colon), you could use the function as shown in cell B7.


<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> ExtractNum(s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        <SPAN style="color:#00007F">With</SPAN> RegEx<br>            .Global = <SPAN style="color:#00007F">True</SPAN><br>            .Pattern = ": -*\d*,*\d+\.*\d*"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    ExtractNum = Replace(RegEx.Execute(s)(n - 1), ": ", "")<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Excel Workbook
	A	B	C	D
1
2	Level: 9Shuttle: 1Estimated VO2max: 43.6 ml/kg/min	9	1	43.6
3	Level: -0.9Shuttle: 10.236Estimated VO2max: 43.6 ml/kg/min	-0.9	10.236	43.6
4	Level: 9Shuttle: 18,000Estimated VO2max: 0.6 ml/kg/min	9	18000	0.6
5
6
7	Level: -0.9Shuttle: 10.236Estimated VO2max: 43.6 ml/kg/min	10.236
Extract Numbers

 

[jonkl02]

SUCCESS!!

Thanks so much Peter! Really appreciate it!

 

[Peter_SSs]

SUCCESS!!

Thanks so much Peter! Really appreciate it!

No problem and thanks for the feedback.

Also a belated "Welcome to the MrExcel board!"