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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
A way using Power Query.

Book1
ABCD
1CodeCode.1Count
2JT005JT010
3JT005BB010
4BB005BD010
5BB005
6BD010
Sheet1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitPos = Table.SplitColumn(Source, "Code", Splitter.SplitTextByRepeatedLengths(3), {"Code.1", "Code.2"}),
    ToNum = Table.TransformColumnTypes(SplitPos,{{"Code.2", Int64.Type}}),
    GroupSum = Table.Group(ToNum, {"Code.1"}, {{"Count", each List.Sum([Code.2]), type number}})
in
    GroupSum
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
What cells are those values in? Will there be more than one row of values? If so, are each row to be treated separately or en mass?
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
Another way using VBA.

Book1
ABCD
1CodeIDTotal
2JT005JT010
3JT005BB010
4BB005BD010
5BB005
6BD010
Sheet1


VBA Code:
Sub Group()
Dim r As Range:         Set r = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")
Dim Key As String
Dim Valu As Integer

For i = LBound(AR) To UBound(AR)
    Key = Left(AR(i, 1), 3)
    Valu = Right(AR(i, 1), 2)
    SD(Key) = SD(Key) + Valu
Next i

Set r = r.Offset(, 2).Resize(SD.Count)
r.Offset(-1).Value2 = "ID"
r.Offset(-1, 1).Value2 = "Total"
r.Value2 = Application.Transpose(SD.keys)
r.Offset(, 1).Value2 = Application.Transpose(SD.items)

End Sub
 

xystential

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

What cells are those values in? Will there be more than one row of values? If so, are each row to be treated separately or en mass?
I will need to confirm but I was going on the assumption that they will populate columns A:E, up to an unspecified amount of rows. They are used to determine commission payouts so they would be re-populated with new entries at regular intervals, perhaps monthly? I'll need to get some more info on that.
 

xystential

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thank you for all the expedient suggestions. I will collect more necessary information to make the intended outcome clearer.
 

xystential

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

What cells are those values in? Will there be more than one row of values? If so, are each row to be treated separately or en mass?
I should say that, at any any time the formula would be applied, it would be en masse to the data that was currently populating the columns and rows but the number of columns and rows and the entries occupying the cells would change on a regular basis.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
So you are not doing this row-by-row... you want to process all of the cells for all the rows and columns that have data, correct?

Where will the first piece of data be located... cell A2 maybe?

Where did you want the output to go?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
@DanteAmor ,

Please tell me you are not leaving because of something I said or did, I would hate to think I was the cause of your withdrawing from this forum. I consider your contributions in this forum to be quite valuable and they will be sorely missed. Is there anything I or anyone else can say or do to convince you to change your mind and continue volunteering here? Please, please stay!

I am a little confused by your statement where you said "You have blocked the conversations...". When have I done that? I don't believe I have ever blocked a conversation (that I can think of). To the best of my knowledge, I welcome all comments... I am not infallible and I learn from those who comment and/or correct things I have posted.

By the way, I appreciate your kind words about my contribution to this forum. I do think consider myself to be at the expert level you seem to think I am at, but I do appreciate your saying so, so thank you for that.
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,127,381
Messages
5,624,360
Members
416,024
Latest member
Illissa

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