Split Cell out into specific character count and concatenate

[EAG1]

Hello all,

We are trying to automate some product titles and could do with some help. The data below is what we have and the following is what we want the results to be. Essentially we have a character limit of 80 and the columns in yellow are what we are looking at. The "combined" column G is counted and this (column H) is then taken away from the total count of 80 to give us how many spare characters we have left (column I). We then want to split the model data (column J) out into the amount of character left (splitting via the comma) but then turning the comma & space into a single space. See further down

The output we are looking for is below. Hopefully it makes sense.

Please let me know if you need any further info or if anything doesn't make sense.

Thanks, EAG1.

 

[Skybluekid]

Do you want a formula or macro solution?

 

[EAG1]

Do you want a formula or macro solution?

Hello, I assumed it would need to be a macro solution, however if someone can suggest a formula to do the job that will also be fine. Thanks

 

[mmhill]

No macro needed.

=IF(MID(SUBSTITUTE(G2&" "&J2,",",""),80+1,1)=" ",LEFT(SUBSTITUTE(G2&" "&J2,",",""),80),(G2&" " & LEFT(SUBSTITUTE(J2,",",""),SEARCH("#",SUBSTITUTE(LEFT(SUBSTITUTE(J2,",",""),80-LEN(G2 & " "))," ","#",(80-LEN(G2)-1)-LEN(SUBSTITUTE(LEFT(SUBSTITUTE(J2,",",""),80-LEN(G2 & " "))," ",""))))-1)))

All you need is the "Combined" text, the model data text, and the max number of characters (80). Don't need the Count and Characters Left columns.

First, there's an IF - if the 81st character (after stripping commas) is a [space], then get the left 80 characters. If not, this formula ... strips the commas, counts the spaces, finds the last space before the max character number and gets all characters up to that number.

I'm on Excel 2016. Prople with more recent versions and functions may be able to do something sexier.

 

[mmhill]

Macro if you prefer ... in cell, type "=GetDesc(G2,J2,80)"

Public Function GetDesc(Combined$, ModelData$, MaxChar%) As String
Dim strTotal$
    strTotal = Combined & " " & Replace(ModelData, ",", "")
    GetDesc = Left(strTotal, MaxChar)
    If Mid(strTotal, MaxChar + 1, 1) = " " Then Exit Function
    GetDesc = Left(GetDesc, InStrRev(GetDesc, " ") - 1)
End Function

 

[EAG1]

Hello @mmhill, thank you for your reply.

I can see that it works for the first instance but how do I get it to work for the subsequent titles? We have more model data than will fit into a single title so need this split across multiple titles (see the output screenshot). The combined words will stay the same each time but we will need to break the models out into multiple titles until they are all used up. Hope that makes sense.

Thank you for your help, it is greatly appreciated.