Formula to create an ID based on words in a cell?

[JohnClifford]

Hi there.

I'm looking for a way to isolate all words in a cell so that I can create an ID based on them. I've found formulae for finding the total number of words, and one for finding a finite number of words, but the problem is I don't know ahead of time how many words there will be. I need to return the first four letters from the first word and the first letter of the second if there are two--but the first three letters from the first, the first letter of the second and the first from the third if there are three, and so on.

I'll give some examples as I'm sure that didn't explain what I need very well:

This piggy - needs to return THISP
This little piggy - needs to return THILP
This little piggy went - needs to return THLPW

Does anyone know if there's a way to do this with a formula? Thanks in advance for any help you can give.

 

[jasonb75]

You could do it with a formula, but it would be a long one.

This might not be foolproof, but it works for your examples.

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Array formula, confirm with Shift Ctrl Enter

=UPPER(LEFT(A2,5-(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))&ACONCAT(IF(MID(A2,ROW($1:$100),1)=" ",MID(A2,ROW($2:$101),1),"")))

I would guess based on your criteria that 5 words would mean take first letter of each, but what if there are more than 5 words?

This solution would give an error at that point, the correction would depent on the result expected.

 

[JohnClifford]

Thankfully for the purpose it's going to be a very rare occasion if ever that the input will be more than 4 words.

Sektor/all, a small request to expand on this (yeah, this was just the first part) :P

The full ID actually needs to be 21 digits; I'll explain how this needs to be worked out using the previous examples, using spaces to break up the sections and avoid confusion:

This piggy - THISP TPA01 TPA10 TPA101
This little piggy - THILP TLP01 TLP10 TLP101
This little piggy went - THLPW TLP01 TLP10 TLP101

So basically the first part we've already established: it's always 5 characters and depends on the number of words.

The second part works on number of words as well, but also depends on another column--for demonstration purposes we'll call it "Destination", which provides the number via an index that starts at 01.

So let's say we're working with "This piggy". First we take the first letter of each word: TP. But the section needs to be 5 characters so we fill it out with the first index-based letter of the alphabet, in this case A.

For the number, if "Destination" doesn't already exist it will be the current number of the index, which will be 01 to start with. If the destination already exists in the list of records, the number needs to be the same as the first record that had it. If it's new, the index needs to increment by 1, with a total limit of 9.

So:

Record 1: Destination "Market" - number is 01
Record 2: Destination "Market" - number is 01
Record 3: Destination "Home" - number is 02
Record 4: Destination "Roast beef" - number is 03
Record 5: Destination "Home" - number is 02

The third section follows the same rules for the letters but for number it starts at 10, then 20, then 30 etc. It also depends on a totally different column than "destination" but follows the same rules as it does regarding uniqueness.

Fourth section is the same but it starts at 101, then 102, then 103 etc.

All sections can only have 10 unique values total.

I understand that this is hugely complicated (how I wish it weren't, but this wasn't my idea. I still have to find a way to automate the generation!) so if anything needs further explanation please let me know.

I'm quite good at Excel but by the grace of the gods this one had me stumped.

 

[JohnClifford]

Okay, ignore the above, I've figured out part of it but I'm still having trouble with the numbers.​

Let's say I have the following spreadsheet (apologies for the formatting, wasn't sure how else to do it on a forum)​

Top Level        Subcategory 1   Subcategory 2   Entry
This little piggy  went to                             market
This little piggy  stayed            at                home​
This little        piggy             had               roast beef
This               little piggy      had               none
And this little    piggy went        WEEWEEWEE all     the way home

Using the ID generation algorithm that I need to find a way of automating, we'd get the following IDs:

1. THILPTLP01TLP10TLP101
2. THILPTLP02TLP20TLP201
3. THISLTLI01TLI10TLI101
4. THIS0THI01THI10THI101
5. ANDTLATL01ATL10ATL101


The logic is as follows:

• Each "index" starts at 01 (subcat 1), 10 (subcat 2) and 101 (entry) respectively.
• If the same letters in subcat 1/2 have already been used in a previous row, the numbers need to increment by 1. (02, 20 etc)
• The first number of the entry will be the same as the first number of subcat 2.
• If the entry has the same subcat 1 and subcat 2 of a previous one its last number will increment by 1 (102, 103 etc)
• "blank" subcat 2s still need to have a unique number, and if there's a blank in a different subcat 1 it needs to be different from other blanks as well.

I can't for the life of me figure out how to do this in VBA.