extract number from text and number cell

Hi South, welcome to the forum.

Assuming your numbers are in A1:A10, in B1 use:

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

Fill down to B10. Hope that helps!

Maybe try...

=MID(A1,FIND(" ",A1)+1,LEN(A1))

You're welcome and glad it worked for you

Please do not forget to mark the thread as solved if this satisfies your query.

The formula I provided works just fine.

If cell A1 has "sr 521" and you put my formula into B1, B1 will show: 521

Same for your other examples. If your data ALWAYS has the text "sr " in front of the number, then a simple MID function like the one jeffrey provided will suffice. Jeffrey's formula works by finding the first space character and returning everything to the right of it. If your text in A1 were "sr bob cat 292", that formula would return "bob cat 292" while mine would still return just 292.

Note also that the MID formula will return a text (string) result, while mine will return a numeric value. It may or may not make a difference depending on what you're doing with that information.

Originally Posted by south

sr 521 521
sr 25 25
sr 5 5

In cells A1 to A3, I have characters listed in the left side. In other cells, I would like to get only numbers without sr.

H Paul,

With the second sample the OP posted...

sr 521 521

returns 521 with your formula

I'm guessing the OP wanted 521 521

My understanding of the question was that those were two different cells.

"sr 521" in one cell, and the user wanted just the 521 in the cell next to it. Even in the re-phrase after my original post, that's how it appeared to me. Unfortunately the forum doesn't apply spacing to columns well (at all) unless you use the CODE tags. Only the user knows best.

Yes I see...could be...

and BTW

I used your formula and it worked fine as of course you already know

I bow to others that came up with it and have applied it before me.

Step through it using Evaluate Formula in the ribbon.

I'd suggest testing on a short alphanumeric string (let's say 10 characters) in A1, and change ROW($1:$10000) to ROW($1:$10), so you can see how it's calculating.

@misys.til

don't break in with your own question in anotherone's topic.

it is against the forumrules.

in that case you have to make a new question and refer to this one.