Extracting numbers from a cell entry

[Womp]

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.

 

[Krishnakumar]

Hi,

One way..

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""))

HTH

 

[Womp]

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]

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]

Thanks Kris.

That's exactly what I was after!

Cheers.

 

[hiker95]

Womp,

Excel Workbook
	A	B	C	D
1	(18-1-4)	18	1	4
2	(200-50-100)	200	50	100
3	(200-555-100)	200	555	100
4
Sheet1

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

 

[hiker95]

Krishnakumar,

Very nice!

This will go into my archives.

 

[Womp]

Thanks Hiker for replying, much appreciated.

 

[T. Valko]

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