Excel 2007 : Separating text from numbers in a single cell

**RickCov** · 01-17-2012, 12:09 PM

Hello all,

I know this has probably been posted before but i'm having trouble finding a solution. I am trying to separate the numbers (possibly without the brackets) from the text, and display only the numbers in the adjacent cell. Please see example below..

Aberdeen (60659)
Aberdeen (60659)
Aberdeen (60659)
Aberdeen (60659)
Aberdeen B.A (60786)
Aberdeen B.A (60786)
Aberdeen B.A (60786)
Aberdeen B.A (60786)
Aberdeen B.A (60786)

Im hoping someone can help with this as this forms part of an urgent assignment appointed by my boss!

Many Thanks
Rick

**OnErrorGoto0** · 01-17-2012, 12:16 PM

You could
Select the data
Data-Text to columns
Delimited
Other - enter ( in the text box.
Finish.
Then in the new number column do a find and replace and replace ) with nothing.

**tigeravatar** · 01-17-2012, 12:19 PM

RickCov,

Welcome to the forum!
Assuming the numbers are always in parentheses, and that those parentheses are the only ones in the cell, you should be able to use this formula to extract the number:
=--TRIM(MID(SUBSTITUTE(A1,")",REPT(" ",99)),FIND("(",A1)+1,99))

**Fotis1991** · 01-17-2012, 12:28 PM

Hi

...Or with formula..

For TExt>>=LEFT(D3;MATCH(1;ISNUMBER(MID(D3;COLUMN($1:$1);1)*1)*1;0)-1)

Array formula>>>CSE

For numbers>>=LOOKUP(9^9;1*RIGHT(TRIM(D3);COLUMN($2:$2)))

Hope to helps you.

**RickCov** · 01-17-2012, 12:31 PM

Thanks to you both for the speedy response!!

OnErrorGoTo() - Thanks, I had tried the text to columns function but the only problem is that I had to repeat this for several thousand incidents.

Tigeravatar - This worked perfectly thank you so much! I think you may have just saved my skin!

I like this forum already!

EDIT: Thanks Fotis, all sorted now