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

#### iceshark412

##### New Member
Hello all,

Consider the following four strings:

1400UPS4736

1401ALUM0001

1401VLV4188A

 1401CNVS4279

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Give this formula a try...

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

• iceshark412 and iliace
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!

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

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).

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)

Replies
6
Views
217
Replies
16
Views
451
Replies
9
Views
337
Replies
11
Views
2K
Replies
29
Views
3K

### Forum statistics

1,196,368
Messages
6,014,854
Members
441,850
Latest member
peh16 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back