grabbing the first and last four numbers of a string that may or may not contain letters

[iceshark412]

Hello all,

Consider the following four strings:

[TABLE="width: 135"]
<tbody>[TR]
[TD="class: xl63, width: 135"]1400UPS4736

[TABLE="width: 135"]
<tbody>[TR]
[TD="class: xl65, width: 135"]1401ALUM0001

[TABLE="width: 135"]
<tbody>[TR]
[TD="class: xl65, width: 135"]1401VLV4188A

[TABLE="width: 135"]
<tbody>[TR]
[TD="class: xl65, width: 135"]1401CNVS4279[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Some have three letters in the middle, some have four. Some end in a number, some end in a letter. What I need to do is to extract the first and last four actual numbers from these strings, to give an output such as:

1400-4736

1401-0001

1401-4188

1401-4279

I have tried various options but I am thinking I may as well seek help while I continue to search for a solution.

So far what I have come up with is:

=IF(ISNUMBER(RIGHT(A2,1)),LEFT(A2,4)&"-"&RIGHT(A2,4),LEFT(A2,4)&"-"&(MID(A2,LEN(A2)-4,4))

But I am having no luck with that. It seems to confuse the order of my if-then statement, and it only works correctly on strings that have letters at the end rather than the "easy" ones without the letters. there are over 12,000 data points I must apply this to, can somebody please help?

Thank you.

 

[Rick Rothstein]

Give this formula a try...

=LEFT(A2,4)&"-"&MID(A2,LEN(A2)-3-NOT(ISNUMBER(-RIGHT(A2))),4)

 

[iceshark412]

Beautiful, thank you Mr. Rothstein!

Could you please explain the formula so that I may learn the logic behind it?

=LEFT(A2,4)&"-"& ------ left four numbers of cell A2 plus a "-"

MID(A2,LEN(A2)-3-NOT(ISNUMBER(-RIGHT(A2))),4) ----- I am lost here. Middle of Cell A2, starting from...?

Either way, thank you so much!

 

[Rick Rothstein]

Beautiful, thank you Mr. Rothstein!

Could you please explain the formula so that I may learn the logic behind it?

MID(A2,LEN(A2)-3-NOT(ISNUMBER(-RIGHT(A2))),4) ----- I am lost here. Middle of Cell A2, starting from...?

If you did not have any letters on the end, the above would have been this...

MID(A2,LEN(A2)-3,4)

The LEN(A2)-3 part just means start from the last character position (which is what the LEN function calculates) and back off three more positions which means you are on the fourth character back, then, starting there, grab 4 characters to the right. That works fine when there is no trailing letter. If there is a trailing letter, then starting on the last character is the wrong location (because it is not a digit), so we need to back off one additional position when there is a letter on the end. Right(A2) will retrieve the last character, we put a minus sign in front (the same as multiplying by minus one) to convert the string number to a real number if possible (only possible if the last character is a digit) and we feed that to the ISNUMBER function (it returns TRUE if the last character is a digit and FALSE otherwise. But we only want to back off one position if the last character is not a digit, so we apply the NOT function to the result from the ISNUMBER function so that we get TRUE for a non-digit and FALSE for a digit. Now, in Excel, when we use a Boolean value in a numerical calculation, TRUE becomes 1 and FALSE becomes 0.... so if the last character was a non-digit, we end up subtracting the 1 that the TRUE got converted to (because we are subtracting which is the mathematical expression that is forcing the conversion)... and if the last character was a digit, then we end up subtracting 0 instead (but subtracting 0 does not change the value of the rest of the calculation. So, the net effect of all this is when the last character is a digit, the calculation you asked breaks down to this...

MID(A2,LEN(A2)-3-0,4)

and when the last character is a non-digit, it breaks down to this...

MID(A2,LEN(A2)-3-1,4)

 

[iceshark412]

Very pleased with your answer, and thankful of course.

 

[István Hirsch]

This formula gives similar results for the examples given:

=LEFT(A1,4)&"-"&IFERROR(1*MID(A1,8,4),MID(A1,9,4))

If the 8th character is a digit (there are only 3 letters in the middle of the string) it concatenates the number starting at the 8th character, mid(a1;8,4) (value); if the 8th character is a letter, then it concatenates the number from the 9th character (value if error). In the latter case error is caused by the 1* in front of the mid function, which tries to transform the string into a number and indicates error if the string following it cannot be regarded as a number (for example M000 or S427).

 

[Rick Rothstein]

This formula gives similar results for the examples given:

=LEFT(A1,4)&"-"&IFERROR(1*MID(A1,8,4),MID(A1,9,4))

If the 8th character is a digit (there are only 3 letters in the middle of the string) it concatenates the number starting at the 8th character, mid(a1;8,4) (value); if the 8th character is a letter, then it concatenates the number from the 9th character (value if error). In the latter case error is caused by the 1* in front of the mid function, which tries to transform the string into a number and indicates error if the string following it cannot be regarded as a number (for example M000 or S427).

Good observation about the 8th character... I like it. Now, assuming that observation is universally correct for the OP's data, I would point out that your formula uses IFERROR which is available in XL2007 and above... using your observation, we can write the formula this way and save a couple of characters without the XL2007 and above restriction...

=LEFT(A2,4)&"-"&MID(A2,8+ISERR(-MID(A2,8,1)),4)

 

[István Hirsch]

Thank you, Rick, nice addition.