Isolating number within text

[wanchope]

Hi Guys,

I'm fairly new to excel and having some difficulties. I have a series of data which is formatted with numbers in between text e.g. rs710521(G;G), rs5275(C;T), rs12376089(A;A) etc. What I would like to do is isolate the number from the text. Is there a function I can employ to do this en masse?

Thanks in advance for any tips

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bebo021999]

With your text in A1, try this array formula

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[AZ-XL]

assuming that your data starts from a1 cell then
b1 =MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),SUM(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

Hit Ctrl+hift+Enter after pasting the formula

[bebo021999]

@AZ-XL,
Your nice formular works for contiguous numbers as sample from OP.

My formula works in generic, including non contiguous numbers like this:
abc12/3xyz

[AZ-XL]

@AZ-XL,
Your nice formular works for contiguous numbers as sample from OP.

My formula works in generic, including non contiguous numbers like this:
abc12/3xyz

Hi Bebo.
Yes you are right. Seeing wanchope's example I suggested such solution. There is no doubt your formula is much useful.
Thanx