iceshark412
New Member
- Joined
- Dec 27, 2013
- Messages
- 3
Hello all,
Consider the following four strings:
<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.
Consider the following four strings:
1400UPS4736
<tbody> </tbody> |
<tbody>
</tbody>
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.