Thank you so much! The first formula works.
Can you explain this formula? I want to learn how to solve these problems myself.
Let me give this a try although I might leave out some parts you deem as important
Feel free to ask for further clarification, I will be sure to provide the ones i have answers to
EXPLANATION
The Logic behind the formula is to pick all consecutive 5 characters(could be numbers) that can be extracted from the string 1.e from the 1st character, pick 5 characters, from the second character pick 5 and on and on which in your case would be "22047","2047 ","047 - ","47 - C", "7 - Cj"
That’s what this section does
MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5)
What you want to check now is to ensure that the strings9all the set of 5 characters) it has selected only contains numbers, hence i used ISNUMBER which tells me true for numbers and false for non-numbers, the new formula at this stage gives us
ISNUMBER(1*MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5)) and it will return TRUE for 22047, TRUE for "2047", FALSE for "047 - "
What I then do is to use the MATCH Function to get the first instance of TRUE 'cos we would be having something like
{TRUE,TRUE,FALSE,FALSE........)
The match in this case gives us 1 as the first instance of TRUE meaning the first string in the set of strings meets our criteria which is 5 digits and they are all numbers
So you wrap that in the MID to say, give me five characters starting from where the match starts from an give me 5 characters
That’s the basic thing(I hope I have explained well)
But you will see I use like two IF statements in there
1. One IF is to ensure that we don’t have a string of 5 numbers starting with 0, cos you seem to want the numbers but not starting with the zero
so
IF(LEFT(MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5),1)<>"0"
2. second IF to be sure the first or last character/number in the string is not a SPACE cos "2047 " will give you true when i use ISNUMBER, so to avoid that I used an IF i.e.
IF(LEFT(MID(A1,ROW(INDIRECT("1:" &LEN(A1)-4)),5),1)<>" " but this only takes care of the first character bot being a space 'cos " 2047" will give TRUE but you don’t want this
So the summary of the whole formula is
1. PICK 5 characters starting from position 1,2,3.......length(string)-4 so you don’t pick characters that don’t exist in the string, that’s why i stop at len(string)-4
2. Check that the first character is not a 0 and that the first character is also not a SPACE, once these 2 conditions are met then
3. USE MATCH to give me the first set of 4-characters that satisfy 2 above and are all numbers
4. Once you get the starting position of the SET that satisfies the condition, pick 5 characters.
