I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

Thanks

If ID numbers at end are always 6 characters and let's say data is in Column
A with string beginning in A1 then in B1 you could type =right(A1,6). This
returns 202268 in your example.

"Michael" wrote:

I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

Thanks

Assuming your records are housed in A1:A1200

A bit cumbersome but try -

=MID(A1,MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:" &LEN(A1))))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN( A1))))))+1,LEN(A1)-MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1) )))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1)))))) )

Hit Ctrl+Shft+Enter

-sk

Michael wrote:
I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

Thanks

Absolutely fantastic, thanks very much

Thanks
Michael

"sk" wrote:

Assuming your records are housed in A1:A1200

A bit cumbersome but try -

=MID(A1,MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:" &LEN(A1))))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN( A1))))))+1,LEN(A1)-MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1) )))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1)))))) )

Hit Ctrl+Shft+Enter

-sk

Michael wrote:
I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

Thanks

Consider the simpler array formula
=MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))*N(MID(A1,R OW(INDIRECT("1:"&LEN
(A1))),1)=","))+1,1024)

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Absolutely fantastic, thanks very much

Thanks
Michael

"sk" wrote:

Assuming your records are housed in A1:A1200

A bit cumbersome but try -

=MID(A1,MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:" &LEN(A1))))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN( A1))))))+1,LEN(A1)-MAX(IF(ISERR(FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1) )))),"",FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1)))))) )

Hit Ctrl+Shft+Enter

-sk

Michael wrote:
I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

Thanks

"Tushar Mehta" wrote...
Consider the simpler array formula
=MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))
*N(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=","))+1, 1024)
....

Gosh, why not consider doing it manually?

Where to begin deconstructing this? Let's start with the inconsistency of
recommending against using undocumented lookup/match functionality but
recmmending using N()'s undocumented behavior when fed arrays of booleans.
Next there's explicit use of ROW(INDIRECT("1:"&LEN(A1))) rather than using a
defined name with a fixed upper bound, but (what the heck) using an
arbitrarily large 3rd argment to the outer MID call.

If you want to use only explicitly documented functionality of built-in
functions only, shouldn't you use

=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=",",
ROW(INDIRECT("1:"&LEN(A1))))))

? Otherwise it's your preferred undocumented behavior against someone else's
undocumented behavior.

In article ,
says...
? Otherwise it's your preferred undocumented behavior against someone else's
undocumented behavior.


Oh, really?

HLOOKUP, VLOOKUP, LOOKUP return incorrect values in Excel
http://support.microsoft.com/default...b;en-us;181201

There's a similar article about MATCH.

MS has also documented a list of XL functions that accept arrays as
arguments. Of course, given how incompatible MS's search algorithms
and my way of thinking are I cannot find it after 30 minutes of
searching. But, I believe N() is on that list.

The bottom line is this. All of you who enjoy exploiting *documented*
bugs in XL are welcome to do so. If -- and yes I know all about pigs
not yet becoming airbore -- MS fixes those holes in its software you
will have no one to blame but yourself. However, hoisting those
"solutions" onto others *without* warning about what you are doing is
what I object to.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
"Tushar Mehta" wrote...
Consider the simpler array formula
=MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))
*N(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=","))+1, 1024)
...

Gosh, why not consider doing it manually?

Where to begin deconstructing this? Let's start with the inconsistency of
recommending against using undocumented lookup/match functionality but
recmmending using N()'s undocumented behavior when fed arrays of booleans.
Next there's explicit use of ROW(INDIRECT("1:"&LEN(A1))) rather than using a
defined name with a fixed upper bound, but (what the heck) using an
arbitrarily large 3rd argment to the outer MID call.

If you want to use only explicitly documented functionality of built-in
functions only, shouldn't you use

=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=",",
ROW(INDIRECT("1:"&LEN(A1))))))

? Otherwise it's your preferred undocumented behavior against someone else's
undocumented behavior.

Michael wrote...
I have a address string (variable length) with ID numbers at the end and want
to be able to identify just the array at the end which could be any number in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

If all these records have the same number of commas in them, copy the
range and paste it in another range, select the entire range of records
and run Data Text to Columns, choose Delimited, then choose Comma as
the delimiter, choose to skip all fields except the last one, then
parse the records (click OK).

If the number of commas differs between records, but you always want
the field after the final comma, define a name like seq referring to'

=ROW(INDIRECT("1:1024"))

and if your first records were in cell A2, use the following formula in
B2 to pull the final field.

=MID(A2,LOOKUP(2,1/(MID(A2,seq,1)=","),seq)+1,1024)

Harlan
That is awesome and works as described.
I have been trying to work out how it works, but can't quite figure it out.
Could you explain?

--
Regards
Roger Govier
"Harlan Grove" wrote in message
oups.com...
Michael wrote...
I have a address string (variable length) with ID numbers at the end and
want
to be able to identify just the array at the end which could be any number
in
length example
Pharma-e,, ,Albert House, South Esplanade, Guernsey, Channel Islands,
,GY1 1AN ,(01481 736 736), ,Pharmacy, , ,202268

as I want to use the number for a lookup, please can anyone help with a
formula as there are 12,000 records.

If all these records have the same number of commas in them, copy the
range and paste it in another range, select the entire range of records
and run Data Text to Columns, choose Delimited, then choose Comma as
the delimiter, choose to skip all fields except the last one, then
parse the records (click OK).

If the number of commas differs between records, but you always want
the field after the final comma, define a name like seq referring to'

=ROW(INDIRECT("1:1024"))

and if your first records were in cell A2, use the following formula in
B2 to pull the final field.

=MID(A2,LOOKUP(2,1/(MID(A2,seq,1)=","),seq)+1,1024)

Roger Govier wrote...
That is awesome and works as described.
I have been trying to work out how it works, but can't quite figure it out.
Could you explain?
....
the field after the final comma, define a name like seq referring to'

=ROW(INDIRECT("1:1024"))

and if your first records were in cell A2, use the following formula in
B2 to pull the final field.

=MID(A2,LOOKUP(2,1/(MID(A2,seq,1)=","),seq)+1,1024)

This relies on an aspect of Excel's ordered lookup/matching
functionality that isn't explicitly documented but has always worked
this way. Ordered lookup or matching means looking for nonexact matches
in sorted ranges. The 'matching' value would be the largest value in
the range searched less than or equal to the value sought, e.g., MATCH
with no 3rd argument or 3rd argument positive, VLOOKUP with no 4th
argument or 4th argument TRUE or 1.

This sort of lookup or matching can take advantage of binary search.
The first step in binary search is to bracket the value sought. If that
value is larger than any value in the range searched, Excel seems to
'short circuit' the process by returning a reference to the last number
entry in the range searched.

In the formula above, the 2nd argument to LOOKUP is an array of 1
divided by TRUE or FALSE values. The arithmetic operation coerces the
boolean values to 1 and 0, respectively. 1/1 = 1 while 1/0 = #DIV/0!,
so the last number entry in the array would correspond to the last
comma in the string (up to the 1024th character).

In article ,
says...
Harlan
That is awesome and works as described.
I have been trying to work out how it works, but can't quite figure it out.
Could you explain?


It relies on the fact that XL doesn't validate the arguments to a bunch
of functions, all of the lookup type. If MS ever delivers on its
stated claim of trustworthy computing all these 'tricks' will break.

In this case the garbage spewed by MS's reliance on GIGO happens to be
someone else's treasure. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Tushar

Thank you also for your explanation.
If MS ever delivers on its
stated claim of trustworthy computing all these 'tricks' will break.
Well, we live on a farm and all the pigs here are still on the ground <VBG
--
Regards
Roger Govier
"Tushar Mehta" wrote in message
om...
In article ,
says...
Harlan
That is awesome and works as described.
I have been trying to work out how it works, but can't quite figure it
out.
Could you explain?


It relies on the fact that XL doesn't validate the arguments to a bunch
of functions, all of the lookup type. If MS ever delivers on its
stated claim of trustworthy computing all these 'tricks' will break.

In this case the garbage spewed by MS's reliance on GIGO happens to be
someone else's treasure. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Tushar Mehta wrote...
It relies on the fact that XL doesn't validate the arguments to a bunch
of functions, all of the lookup type. If MS ever delivers on its
stated claim of trustworthy computing all these 'tricks' will break.

Validate arguments? Excel provides no semantic validation in any
function. Excel returns errors or wrong results when fed faulty
arguments. That's how all spreadsheets work. That's how most functional
and procedural languages work too.

Does trustworthy computing extend to any derivative programs produced
by Microsoft's users?

Strictly with respect to lookup and match functions, FWLIW 123 works
the same way as Excel. Approximate matching uses binary search. Binary
search performs bracketting as it's first step. If the lookup value is
larger than the last (valid) value in the lookup range, these functions
short circuit and return references to the last (valid) value. Maybe
this functionality will be 'fixed' at some later date, in which case I
can only hope Microsoft adds reverse lookup and match functions that
search from end to start.

In this case the garbage spewed by MS's reliance on GIGO happens to be
someone else's treasure. {grin}

GIGO? Purely on practical grounds, it'd REALLY slow Excel down if it
had to validate that the first column or row of lookup ranges or arrays
were in sorted order. Maybe you'd like the wait, but I prefer Excel to
behave as it does now.