Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example


"Company XYX 23432 R & P analysis"


without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell


I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.


anyway to do a vlookup and have it search for a numerical value or
something like that?


anyhelp you can give would be greatly appreicated.


Thanks


mithu

I don't know if this is what you are looking for but if the ID is in say H1,
you use the UDF:

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function

in your VLOOKUP() like:

=VLOOKUP(NumberIt(H1),..........rest of vlookup formula


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"mithu" wrote in message
oups.com...
Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example


"Company XYX 23432 R & P analysis"


without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell


I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.


anyway to do a vlookup and have it search for a numerical value or
something like that?


anyhelp you can give would be greatly appreicated.


Thanks


mithu

Try:

=LOOKUP(100^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:"&LEN(A1)))))

"mithu" wrote:

Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example


"Company XYX 23432 R & P analysis"


without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell


I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.


anyway to do a vlookup and have it search for a numerical value or
something like that?


anyhelp you can give would be greatly appreicated.


Thanks


mithu

On Mar 19, 10:35 am, Toppers
wrote:
Try:

=LOOKUP(100^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),RO*W(INDIRECT("1:"&LEN(A1)))))



"mithu" wrote:
Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example

"Company XYX 23432 R & P analysis"

without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell

I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.

anyway to do a vlookup and have it search for a numerical value or
something like that?

anyhelp you can give would be greatly appreicated.

Thanks

mithu- Hide quoted text -

- Show quoted text -

Thanks you guys are awesome that worked perfectly.

Toppers,

Could you not remove the INDIRECT() & LEN() and make it non-volatile?

=LOOKUP(100^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(1:255)))


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"mithu" wrote in message
oups.com...
On Mar 19, 10:35 am, Toppers
wrote:
Try:

=LOOKUP(100^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),RO*W(INDIRECT("1:"&LEN(A1)))))



"mithu" wrote:
Hello..
I am having trouble with extracting numbers from merged cells

i have a top row that has lets say for example

"Company XYX 23432 R & P analysis"

without the quotes.. is there anyway to extract the 23432 which is
the company id from this merged cell

I am doing this because i need to do a vlookup and the 23432 is the
lookup value for this particluar file. .there is about 300 files i
need to update so i dont want to go in one by one and add a new cell
with the company id.

anyway to do a vlookup and have it search for a numerical value or
something like that?

anyhelp you can give would be greatly appreicated.

Thanks

mithu- Hide quoted text -

- Show quoted text -

Thanks you guys are awesome that worked perfectly.