Extract text only

[Arts]

This has been driving me crazy !!!

I have a list of countries with a number on the end (the number is always a single digit)

Ireland1
Uk3
Belgium6

What I am hoping for is a formula to remove the number and just return the text, Ireland, Uk etc. There is no space after the country.

Thanks
Arts

 

[Rick Rothstein]

Thank you for the break down Rick or Lord Rick I should say... I shall try to digest, Thank you once again !

The FIND function is not trying to find the whole number, only the position of each individual digit... that is the array it returns... first the position of the first 0 it finds, second the position of the first 1 it finds, third the position of the first 2 it finds, etc.... all of these first positions form an array of values... the smallest value in that array (what MIN returns) is the position number of the first digit in the text... we don't know what that digit is, nor do we care, we only care that it is the smallest position number of the ten digits that were found.

 

[Arts]

the smallest value in that array (what MIN returns) is the position number of the first digit in the text... we don't know what that digit is, nor do we care, we only care that it is the smallest position number of the ten digits that were found.

Very well explained !!! I am understanding up to the point of the arrays, when we get to the MIN part, you say the MIN returns the first digit in the text, so what happens or when does it pick up the second or third digits in the text if the min is only picking up the first ? Also at the end you have the -1 as part of the left function but if we are only minus one figure how will it minus text that have two digits or three digits. so as per your first formula

=LEFT(A1,LEN(A1)-1)

the minus 1 here takes away the one digit how is this same function taking away multiple digits ?

Hope what I am saying makes sense

Arts

 

[Caribeiro77]

Sorry to enter without asking ( just loved Rick's formula)
This is what the MIN returns:

=LEFT(A1;MIN({9;8;11;12;13;14;15;16;17;18})-1)

the -1 is just subtracting 1 to the minimum value in the array (8).... which returns 7 to the num_chars argument of the LEFT function...

 

[Arts]

Caribeiro, all are welcome no permission required to join

I'm still not getting it, just isn't digesting, the -1 is subtracting 1 in the minimum value in the array so if I have Belgium12 minus 1 from there will give me what in this example....?

What I'm trying to say is probably not making sense is hard to portray the thought into words (if that makes sense)

 

[Caribeiro77]

Ok, if you evalute the "within_text" argument of the FIND you will get this:

=LEFT(A4;MIN(FIND({0;1;2;3;4;5;6;7;8;9};"Belgium120123456789"))-1)

So in the case of Belgium12 the 1 is on the 8th position that's it's why the the FIND function "finds" the 1 in the 8th positon and is passing that information to the MIN, once MIN get that information it subtracts 1 giving you the 7 in this case

Another example is you use fpr example:

Portugal345

=LEFT(A5;MIN(FIND({0;1;2;3;4;5;6;7;8;9};"Portugal3540123456789"))-1)

now "within_text" argument of the FIND the position of the 3 which in this case is the 9th, so the MIN gets that 9 and subtracts 1 which is giving you 8( the num_chars to the LEFT function...resulting on PORTUGAL...

 

[Rick Rothstein]

I am understanding up to the point of the arrays, when we get to the MIN part, you say the MIN returns the first digit in the text, so what happens or when does it pick up the second or third digits in the text if the min is only picking up the first ? Also at the end you have the -1 as part of the left function but if we are only minus one figure how will it minus text that have two digits or three digits. so as per your first formula

=LEFT(A1,LEN(A1)-1)

the minus 1 here takes away the one digit how is this same function taking away multiple digits ?

Lets use a specific example and work through it to see if that helps you to see what is happening. This is what we have...

A1: UK836
B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

Okay, examining the parts using colors..

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

The above red part means the text that will be searched is this...

UK8360123456789

since all we did is concatenate the ten digits on the back of it. Remember, we did this so a search for any digit will find it somewhere even if that somewhere is after the end of the original text. So this is our formula now...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},"UK8360123456789"))-1)

The red part is called an array constant (the curly braces mark its beginning and end, the values inside must be a comma delimited list of constants). The way array constants works is each member of the array is presented to the function (FIND in this case) and evaluated individually, then all those values are placed in an array and returned by the function. So, first, FIND looks for 0 and finds it at position 6... next FIND looks for 1 and finds it at position 7... next FIND looks for 2 and finds it at position 8... next FIND looks for 3 and finds it at position 4... next FIND looks for 4 and finds it at position 10... next FIND looks for 5 and finds it at position 11... next FIND looks for 6 and finds it at position 5... next FIND looks for 7 and finds it at position 13... next FIND looks for 8 and finds it at position 3... next FIND looks for 9 and finds it at position 15. All those found position numbers form an array constant that looks like this...

{6,7,8,4,10,11,5,13,3,15}

which replaces the FIND function entirely meaning our formula becomes this...

=LEFT(A1,MIN({6,7,8,4,10,11,5,13,3,15})-1)

The argument to the MIN function is the array constant that FIND produced... the minimum value it returns is 3 (the next to last value in the array)... that is the position number of the first digit it found when it calculated the position numbers of all the individual digits it searched for. So our formula now becomes this...

=LEFT(A1,3-1)

which reduces to...

=LEFT(A1,2)

and that evaluates to UK which is the text part of A1 without the ending digits.

 

[Arts]

Thank you Caribeiro and Rick, I have just got into work (8.30 am here in the UK) I didn't want you to think your comments had gone un-noticed and shall read into them now.

Arts