• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Concatenate with set number of characters.

S

sonnyatoak

New Member
Trying to figure out how to solve Concatenate formula that would have 80 characters. Since many of the cells would include more than 80 characters, I need the overflow to go to the adjacent cell. On sheet 3 of this file, I have eliminated duplicate routing numbers (found on sheet 1), but haven't put in place the Concatenate formula yet. Our system only allow 80 characters.


https://hotfile.com/dl/176088448/57ca118/Routing_sheet.xls.html
 
Hi sonnyatoak,


This is an 8 MB file!! Please upload a "Sample file" i.e. one with just sample data. :)


Edit: Got it downloaded!! Thanks!


Faseeh
 
Hi sonnyatoak,


Assuming your data is in Cells A2:A4, Enter:


In B2 & drag to B4:
Code: 
=IF(LEN(A2)<=80,A2,LEFT(A2,80))   ....will give 1st 80 Characters

In C2 $ drag to C4: =IF(LEN(A2)>80,MID(A2,81,LEN(A2)-LEN(B2)),"")
... give rest of them.


Hope that helps,


Faseeh
 
Goal is to cut down over 12000 routing entries (sheet 1) down to 4000 (sheet 3)by eliminating duplicate part numbers. Probably easier to understand starting from sheet 1 information. In column A, I have a list of routing numbers. Some numbers appear more than once (because of comments in column E to that part number). Column F has the number of characters in Column E. I would like to reduce column A part numbers where no duplicate part numbers (i.e 4 shown below for example). I would like to merge contents in Column E (I can use Concatenate), but the cell can not have more than 80 characters. Remaining cells up to 80 carried over to another cell Columm G, H, and so on. Also, if I can avoid a word breaking up, that would be ideal. Notice 2nd line, last word HAN and 3rd line first word DLES. Column F is only for Character count. I have included a sample file with the the font in RED for my desired outcome.


https://hotfile.com/dl/176152290/857f245/Sample_route.xlsx.html
 
soonyatoak,


I think this is what you had been looking for, split text, 80 in first column, rest in other column, with words not broken:


http://dl.dropbox.com/u/60644346/ConcatenateWithWordBreak.xlsx


Now you can use CONCATENATE() function to sum up every thing in a cell for a given Serial No. and then use this sheet to split text as you needed. Hope it helps.


Regards,

Faseeh
 
I am very frustrated. The information you have provided is probably correct but I keep coming up with errors. I have downloaded the file for preview. Again, what I am trying to do is eliminated duplicate routing numbers but retain the text. I was able to accomplish the first step; COLUMN F. Bringing over the text; COLUMN G is posing a problem which prevents me from CONCATENATE to finish the 80 character rule mentioned earlier. I keep running into problems. See earlier suggestions that was made in combining texts, # of characters. I have spent days on this and keep coming up with one error or another.


https://hotfile.com/dl/176192119/e137a27/route_66.xlsx.html
 
Hi sonnyatoak,


It is unfortunate that your problem has still been unresolved. I was happy reading your second last post that you have finally found a solution but that is not the case as i can read from your last post.


Let me summarize what i have understood from your post:


1. You have got Routing No.s that have duplicates, Lets say we are considering 09210200600 in cell A5 and A6.


2. You want to eliminate Duplicates from Column A, for out case just ONE entry for 09210200600.


3. You want all the comments/"Converted" into one cell.


4. You want that cell to have only first 80 characters.


5. Rest of characters being placed in the cell to the right.


Is it correct?? .... and btw, please be patient & polite while asking questions :)


Faseeh
 
That's right. Seems simple stating that but I keep messing up when copying and pasting. Information can be put on another sheet as long as the information is there.
 
Faseeh, I didn't mean I was frustrated with anyone who has helped. You guys have been great. I was frustrated at me for not being able to complete the instructions. I hope no one misunderstood my comment as being directed at them. I truely apologize if it was taken that way.
 
Hi sonnya,


There is no need to appologize :D .. I downloaded ur file safely and it was working ok although it was taking time while opening and calculating formulas....


I have alrady reffered this thread to SirJB7 bcz i think its solution will be throughh VBA...


@Ninjas: Kindly look into the thread need ur attention!!


Faseeh
 
Hi soonyatoak,


I have come up with a solution, lets see how it is:


http://dl.dropbox.com/u/60644346/Route66.xlsx


In case you like it, i am explaining the process how i have done it:


1. I created a pivot table that you can readily create and update. If you format your original data as table, there will be no need to update it even, just refresh the pivot table and you will get the unique values from it.


2. Column E contains simple formula drag it to down.


3. Rest of the formulas are set to be dragged right and down. I have assumed that values in your col A will not repeated more then 10 times if they do , adjust the third row to that.


4. Col R will give you all comments and details concatenate for that particular Col A Value.


5. A formula will split the text in first 80 and rest of the text.


Hope that it works for you!!!


Regards,


Faseeh
 
Thanks Faseeh,


I have several questions which might be easier if I sent by email where I could inclose a picture (using snagit) what I'm asking about. Let me know if thats OK? I'm I mistaken or can I assume you just have a sample of what I earlier posted? Looks like 316 items and of course I had over 12,000. I apparently can't add as I am wrong on the "Rest of stuff" category. Looks like many of these have over 80 characters. Our system will only take 80...my apologies.


Let me know if I can email you with the specific areas so I finish this up right. Thanks again for all your help.
 
Hi sonnya,


You can email me at faseeh10@hotmail.com Regarding 80+ characters, i have tested the formula and will try to fix the bug...am waiting for ur email.


Regards,

Faseeh
 
Ok... am uploading to give you dropbox link...


Just replied to your send-email-address. plz check
 
Got It???


Ok here is the linke whenever you comes:


http://dl.dropbox.com/u/60644346/Routing%20sheet%20-%20Workout.xlsx


Regards,
 
You must log in or register to reply here.
Back
Top