Extracting numbers from a cell entry

Womp

New Member
Joined
Apr 3, 2011
Messages
4
Hello all,

I am new to excel, and have a question regarding extracting certain numbers from a cell entry.

For example, I have an entry that reads "(18-1-4) " (without the quote marks, and there is a space after the last bracket if that makes a difference). I need to put the numbers into seperate cells. The numbers range from 0 through to 200. So another example may look like "(200-50-100) ".

Is this possible?

Cheers.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Womp

New Member
Joined
Apr 3, 2011
Messages
4
Thanks for your reply Kris, I appreciate it.

The formula you gave would just erase the brackets in the new cell, correct?

I need to put the numbers into 3 different cells. For example, "(18-1-4) " I want the 18 in one cell, the 1 in another cell and the 4 in another cell.

Hope that makes it clearer.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

In B1 and copy down & across

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"(",""),")",""),"-",REPT(" ",10)),COLUMNS($B1:B1)*10-10+1,10))+0

HTH
 

Womp

New Member
Joined
Apr 3, 2011
Messages
4

ADVERTISEMENT

Thanks Kris.

That's exactly what I was after!

Cheers.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Womp,


Excel Workbook
ABCD
1(18-1-4)1814
2(200-50-100)20050100
3(200-555-100)200555100
4
Sheet1




Just copy the formulae in cells B1, C1, D1, down.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

Krishnakumar,

Very nice!

This will go into my archives.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hello all,

I am new to excel, and have a question regarding extracting certain numbers from a cell entry.

For example, I have an entry that reads "(18-1-4) " (without the quote marks, and there is a space after the last bracket if that makes a difference). I need to put the numbers into seperate cells. The numbers range from 0 through to 200. So another example may look like "(200-50-100) ".

Is this possible?

Cheers.
Try this...

Select the range of cells in question.
Goto Edit>Replace
Find what: (
Replace with: nothing, leave this blank
Replace All

Find what: )
Replace with: nothing, leave this blank
Replace All
Close

With the range of cells still selected...
Goto Data>Text to Columns
Delimited>Next
Select: Other
In the little box enter a dash -
Finish
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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
Top