EXCEL Question

[Marc]

This should be a quick one

I have in a cell the following

ABC - 1234567 - DEF

I want an adjacent cell to just show the 7 digit number. Does anyone know a formula to do this? Need to bear in mind that there won't always be just 3 characters either side of the number so a MID formula won't always work.

Thanks

 

[f1charlie]

Will there always be a hyphen and space before the number?

If so, how about this:

=MID(A1,FIND("-",A1)+2,7)

 

[Marc]

Will there always be a hyphen and space before the number?

Not necessarily. The data will most likely be entered my engineers out in the field so would expect extra spaces, full stops and the like.

 

[Llamaman]

Not necessarily. The data will most likely be entered my engineers out in the field so would expect extra spaces, full stops and the like.

If there's no consistent format in entry, I'd seriously consider changing the input table to split into 3 entries. No matter how sophisticated a formula you come up with, an engineer will find a way to break it!

Failing that...
http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

 

[zlinuk]

Found this;

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

(A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula.

seems to work.

 

[SarahLee]

Found this;

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

(A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula.

seems to work.

Oooh - I'm definitely stealing that.
Very clever.

Just tried it out and it works perfectly too

 

[Marc]

If there's no consistent format in entry, I'd seriously consider changing the input table to split into 3 entries. No matter how sophisticated a formula you come up with, an engineer will find a way to break it!

Failing that...
http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

Not my table, this is what happens when you help someone else, they tell all their friends!

Found this;

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

(A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula.

seems to work.

Works perfectly, thanks.

 

[f1charlie]

Found this;

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

(A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula.

seems to work.

Blimey!

 

[viv1969]

Found this;

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

(A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula.

seems to work.

That is a rather nice bit of cleverness right there.

And duly "borrowed"

 

[Lynton]

or we could move to excel 2013

 

[Marc]

or we could move to excel 2013

Ok, if you could just write me a business case for my employer.

 

[dejongj]

or we could move to excel 2013

Why stop there. Excel 2016 is even more improved The advantage of Office 365.

Btw nice formula, like it a lot.

 

[f1charlie]

Ok who's going to explain it?