Excel Formula for an alphanumeric text string in a cell

xystential

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I'm not really even sure how to query this so I'll provide an example and then explain further:

JT005JT005BB005BB005BD010

These values are pulled in from an agent database. The structure is always a string of 5 characters- the first 2 are alphabetic and correspond to the person's initials and the third is a numerical differentiator for people with the same initials. So 2 people both having the initials JJ, one would be JJ0, the other JJ1. The last 2 digits correspond to commission percentages (05% or 10%). What I'm trying to do is create a formula that identifies each unique 3 letter/number identifier and then adds the last two digits for each and outputs a sum value in the same format. So, for instance, in the above scenario, the formula would see that JT0, BB0, and BD0 are unique. It would then output the sum of the last two digits of each in the same format so- JT010, BB010, and BD010. This formula would need to be robust to handle additions. By that, I mean as new 3 letter/number identifier are pulled in, the formula recognizes them and performs the operation on them.

Thanks to Anyone That Has a Solution
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,425
Office Version
  1. 365
Platform
  1. Windows
I am a little confused by your statement where you said "You have blocked the conversations...". When have I done that?
Rick I think that Dante meant he could not contact you via the message system, as you have it set so only certain members can start a conversation you.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
234
Office Version
  1. 2013
Platform
  1. Windows
Hi Rick Rothstein (Hero without cape):

You have blocked the conversations, I hope I am not inopportune, I just want to write you a few words:

I congratulate you for being one of the best experts on the forum, I really absorbed a huge amount of knowledge reviewing and analyzing your answers.
I appreciate all your interventions that you made in my answers to correct my mistakes.
This was a great experience in my life, but for personal reasons I am going to withdraw from the forum.

I wish you the best in Christmas for you and your loved ones and everyone have a great year 2021.

Receive an affectionate hug.
Dante Amor.
Hello DanteAmor

I am not here since long time but I have seen many of solutions in this forum are great
please do not leave the forum.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
Rick I think that Dante meant he could not contact you via the message system, as you have it set so only certain members can start a conversation you.
Oh, I didn't think of that. Yeah, you are probably right. I figured when the forum software was changed, it would allow all messages to be sent/received. I just checked and it was set to "People you follow" whatever that means. I changed it to "Members only" (only other choice was "Nobody") so I think messages can now be sent to me. Thanks for noting this as it never occurred to me the default under the new forum software would have been anything but "everybody can send a message to you". Maybe is it a good thing Dante had to use this open forum as others reading this thread (I see there was one already) will chime in and ask Dante not to leave. I really do think his offerings are valuable to the community and I would hate to see him leave.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,370
Messages
5,624,290
Members
416,018
Latest member
mirceaon

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
Top