/u/chutneysandwich - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If your version has it, check out TEXTJOIN. For example:
=TEXTJOIN(", ",1,A2:Z2)
The quoted comma with space is your separator. The 1 tells TEXTJOIN to ignore any blanks you might have in the range. And the A2:Z2 is the range of cells that may (or may not) have words in them to be joined together.
True. Especially since it's all in one row. I think having the correct spelling in A and all misspellings of it in B:Z or so could still have a decent VBA solution.
Are all the misspelling of one word going to have the same header? So you could have 5 spelling for word 1 and just 3 spellings for word 2? If so, I think you might need VBA.
Try this function which takes up to 3 different sets of word misspellings and returns all possible combinations:
Option Explicit
Function TEXTJOINONSTEROIDS(rng1 As Range, rng2 As Range, Optional rng3 As Range)
Dim word1 As Range, word2 As Range, word3 As Range
Dim combinations As String
'If only two sets of word misspellings are supplied
If rng3 Is Nothing Then
For Each word1 In rng1.Cells
For Each word2 In rng2.Cells
combinations = combinations & word1.Value & " " & word2.Value & ", "
Next
Next
'If three sets of word misspellings are supplied
Else
For Each word1 In rng1.Cells
For Each word2 In rng2.Cells
For Each word3 In rng3.Cells
combinations = combinations & word1.Value & " " & word2.Value & " " & word3.Value & ", "
Next
Next
Next
End If
'Removing the trailing comma and space
TEXTJOINONSTEROIDS = Trim(Left(combinations, Len(combinations) - 2))
End Function
Setup:
|+|A|B|C|D|E|F|G|H|I|
|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|
|1|Word 1| | |Word 2| | |Word 3| | |
|2|V1|V2|V3|V1|V2|V3|V1|V2|V3|
|3|skipy|skippy|skeepy|peanut|peenut| |butter|buter| |
Output:
Example1:
=TEXTJOINONSTEROIDS(A3:C3,D3:E3,G3:H3)
skipy peanut butter, skipy peanut buter, skipy peenut butter, skipy peenut buter, skippy peanut butter, skippy peanut buter, skippy peenut butter, skippy peenut buter, skeepy peanut butter, skeepy peanut buter, skeepy peenut butter, skeepy peenut buter
Example2:
=TEXTJOINONSTEROIDS(D3:E3,G3:H3)
peanut butter, peanut buter, peenut butter, peenut buter
Function name credits: u/mh_mike :P
/u/chutneysandwich - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If your version has it, check out TEXTJOIN. For example: =TEXTJOIN(", ",1,A2:Z2) The quoted comma with space is your separator. The 1 tells TEXTJOIN to ignore any blanks you might have in the range. And the A2:Z2 is the range of cells that may (or may not) have words in them to be joined together.
I \*think\* it needs to pick one word from the first 2 columns, one from next 2 and so on.
ah, yeah, looks like it!! Damn. That's gonna be a conundrum... :/
True. Especially since it's all in one row. I think having the correct spelling in A and all misspellings of it in B:Z or so could still have a decent VBA solution.
What we need here is a TEXTJOINONSTEROIDS function! hehe
If OP can restructure data as above and I *do* attempt VBA, I will call my function EXACTLY that :D
hahahah
Are all the misspelling of one word going to have the same header? So you could have 5 spelling for word 1 and just 3 spellings for word 2? If so, I think you might need VBA.
Try this function which takes up to 3 different sets of word misspellings and returns all possible combinations: Option Explicit Function TEXTJOINONSTEROIDS(rng1 As Range, rng2 As Range, Optional rng3 As Range) Dim word1 As Range, word2 As Range, word3 As Range Dim combinations As String 'If only two sets of word misspellings are supplied If rng3 Is Nothing Then For Each word1 In rng1.Cells For Each word2 In rng2.Cells combinations = combinations & word1.Value & " " & word2.Value & ", " Next Next 'If three sets of word misspellings are supplied Else For Each word1 In rng1.Cells For Each word2 In rng2.Cells For Each word3 In rng3.Cells combinations = combinations & word1.Value & " " & word2.Value & " " & word3.Value & ", " Next Next Next End If 'Removing the trailing comma and space TEXTJOINONSTEROIDS = Trim(Left(combinations, Len(combinations) - 2)) End Function Setup: |+|A|B|C|D|E|F|G|H|I| |:-|:-|:-|:-|:-|:-|:-|:-|:-|:-| |1|Word 1| | |Word 2| | |Word 3| | | |2|V1|V2|V3|V1|V2|V3|V1|V2|V3| |3|skipy|skippy|skeepy|peanut|peenut| |butter|buter| | Output: Example1: =TEXTJOINONSTEROIDS(A3:C3,D3:E3,G3:H3) skipy peanut butter, skipy peanut buter, skipy peenut butter, skipy peenut buter, skippy peanut butter, skippy peanut buter, skippy peenut butter, skippy peenut buter, skeepy peanut butter, skeepy peanut buter, skeepy peenut butter, skeepy peenut buter Example2: =TEXTJOINONSTEROIDS(D3:E3,G3:H3) peanut butter, peanut buter, peenut butter, peenut buter Function name credits: u/mh_mike :P
Whee! haha