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

iceshark412

New Member
Joined
Dec 27, 2013
Messages
3
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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!
 
Upvote 0
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)
 
Upvote 0
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).
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top