(Excel 2013) Combining two cell value without exceeding certain characters limit

0
votes

I've tried to solve this with excel formula but it is too complicated as I need the workbook to be as less clustered with formulas. So I turned to VBA for aid. So far all the VBA that I've found was mainly for trimming single cell characters only.

What I need for my project is as follows:

A1-Name
B1-Colour
C1-Name(Colour)

If LEN(C1) is less than 81 then nothing is trim. Else, TRIM A1 only without changing B1 so the end result is LEN(C1) is always less than 81.

Thanks.

Edit: Excel Screenshot 1

What I've tried is:

-I will do "len" on B2 in cell AA2 then another "len" on C2 in cell AB2.
-Then I combine B2 and C2 in cell D2.
-Do another "len" for cell D2 in cell E2.
-If the value in E2 exceed 80, then I've to do "right" for cell B2 in another cell.The value that will be deducted from cell B2 is AA2-80+AB2.
-Once it is done, the new trim B2 will be recombine with C2 in another cell.

That is too many new cells. As you can see, I've to hide some cells that make up what is inside cell F2. So here I am. Beaming up that spotlight with that letter "E" so that Excelman will come to the rescue.

1
excelexcel-2013vba
What have you tried in the formula so far? – Ctznkane525
This is a fairly simple formula in Excel - why do you need VBA? But, if you are using VBA, the VBA code you found should work as you only want to shorten one thing (i.e. the value from A1) and then concatenate that with B1. So please post the code you tried and we can help you correct it. – YowE3K

1 Answers

0
votes

A very simple Excel formula which should do what you want is

=LEFT(A1,MAX(0,78-LEN(B1))) & "(" & B1 & ")"

i.e. take as many characters as possible from A1 so that, when concatenated to "("&B1&")", the total won't exceed 80 characters, and then concatenate that to the "("&B1&")".

Note: Cell references in the formula above are based on your original question. Based on the screenshot, and the edits to your question, the formula would be:

=LEFT(B2,MAX(0,78-LEN(C2))) & "(" & C2 & ")"

If you definitely need VBA, you can use the same functions (i.e. Left and Len) and the same operators (i.e. - and &) in VBA as are used in Excel. The only problem is MAX, which will need to be replaced with Application.Max or with an If statement.