Trouble extracting and keeping numbers from an alphanumeric string

Hello,

I am trying to pull the numbers out of an alphanumeric string using the below formula, but Office 2007 will throw up a vauge error and highlight the $1 character:

Please Login or Register  to view this content.

I'm trying to take column A:

abc123
123abc
ab123c

and generate column B:

123
123
123

I'm new ot Excel, so this may very well be somehting simple I have or haven't done correctly.
Thanks in advance.

Hi hammer32,

Did you tried to confirm the formula using Ctrl + Shift + Enter not just Enter..

Regards,
Deb

Deb,

Yes, thank you. The error I get is:

"The formula you typed contains an error."

Works okay for me. Do you normally use semicolons instead of commas to separate parameters in formulae?

Hope this helps.

Pete

Pete,

Today would be my first time using formulas in Excel.

Hi Hammer...

then welcome to the rude world..

* Copy the above formula..
* Selcect B1, Press F2, and then paste Formula..
* Now HOLD Ctrl & Shift, and press ENTER.

* Now start giving data in A1.. A2.. and drag the formula from B1 to downward by using mouse, on the Right bottom of cell B1..

Regards,
Deb

Trying a smaller subset of the above formula,

Please Login or Register  to view this content.

When I hit Ctrl-Shift-Enter Excel highlights the "($1:$9)" and in a dropdown window says "ROW([reference])"

Sorry, I'm pretty new at this.

Originally Posted by Debraj Roy

Hi Hammer...

then welcome to the rude world..

* Copy the above formula..
* Selcect B1, Press F2, and then paste Formula..
* Now HOLD Ctrl & Shift, and press ENTER.

* Now start giving data in A1.. A2.. and drag the formula from B1 to downward by using mouse, on the Right bottom of cell B1..

Regards,
Deb

Deb,

No dice, same error and it highlights the $1 in B1 after holding Ctrl & Shift and pressing Enter.

Sean

Originally Posted by oeldere

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Courtesy of:*Ron Coderre

Ron,

Thank you, my machine also highlights the $1 in this formula as well. Could there be some setting whereI have to enable Excel to be able to process the $ character?

Thanks,

Sean

Originally Posted by oeldere

and if you open my file, you get the same faillure (on $1)?

Ron,

It works just fine on your file... How odd, it must be a settings thing somewhere.

Oeldere,

So the formula you typed above with the $1 gives the same error all the time, but in the excel workbook you sent the formula:

Please Login or Register  to view this content.

which does work in your workbook and new workbooks, but not in the workbook I've been working on with my data. There, it highlights the RC[-1] and doesn't know what to do with it. The R1 expression causes the 'R' row to be highlighted.

Originally Posted by XOR LX

Hi,

You're not set to R1C1 reference style or something (Options→Formulas)?

Regards

Thank you!

Originally Posted by XOR LX

Hi,

You're not set to R1C1 reference style or something (Options→Formulas)?

Regards

Thank you!

Originally Posted by oeldere

Since it is working on my an your new workbook, maybe your file is corrupt.

A suggestion => Is it an option to copy your data to a new workbook and try it in the new workbook.

I will, thank you for bearing with me.

No worries, I could have spent days on this problem.

It was both the R1C1 and my inability to code.

Thanks Xor LX, you pointed in the right direction. I was also suspicious why it is not working for hammer32 and for everyone else working fine. Hammer32 here is what you can try to make things right. Go to Excel options (by clicking the office button on top left corner), then Formulas and there you will find "R1C1 reference style", uncheck if it is checked. Now even if you enter your formula which you provided in the beginning of the thread and then pressing Ctrl + Shift + Enter..... Your formula should work now.
Hope that will help