Extract number from a combined text|number cell

JNowell

New Member
Joined
Jun 15, 2015
Messages
11
Excel for Mac 2011

I need to extract a number from within a cell of text. The number is not located in the same place and there could be other numbers within the cell. I have to go cell-by-cell to visually key these numbers in. There are thousands of rows. Any help would be appreciated.

Cell Examples:
250 2x4
400 FIS 2x4
450 TRV
Maverick 1000
Renegade 800
Brute Force 750 EPS
Teryx 750 4X4
Sportsman 570 EFI X2


The result needed is:
250
400
450
1000
800
750
750
570

Does anyone have suggestions on how I can get these results?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Here's a suggestion, assuming there is only one number in each cell.

- Select whole column of data (assuming column A)
- Go to Data > Text to columns, Delimited, Space
- Insert a new column in A
- In A1, add the formula "=Max(B2:B100) and copy down

You should get this :

[TABLE="width: 333"]
<tbody>[TR]
[TD]250[/TD]
[TD]250[/TD]
[TD]2x4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]400[/TD]
[TD]FIS[/TD]
[TD]2x4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]450[/TD]
[TD]450[/TD]
[TD]TRV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Maverick[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]Renegade[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]Brute[/TD]
[TD]Force[/TD]
[TD]750[/TD]
[TD]EPS[/TD]
[/TR]
[TR]
[TD]750[/TD]
[TD]Teryx[/TD]
[TD]750[/TD]
[TD]4X4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]570[/TD]
[TD]Sportsman[/TD]
[TD]570[/TD]
[TD]EFI[/TD]
[TD]X2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I must be doing something wrong. I can not get this to work. The columns separate fine using the Text to columns action. I insert a new column "A" and paste the formula but it delivers 1000 in each field.
Any idea of what I could be missing?
Joanie
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,471
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top