Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-26-2018, 12:07 AM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default Concatenate - function help

Hi there,



I need the function which helps to concatenate the cell values which is separated by comma as follows:

If cell J5 ------> A1,B1,AB1....etc which needs to be converted as follows in to cell M5

<categ no = "A1"/><categ no = "B1"/><categ no = "AB1"/> etc....

Can you please anyone help me out on this?

thanks
Ganesan. G
Reply With Quote
  #2  
Old 12-26-2018, 12:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,335
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Please post a sample sheet with some data and expected results
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 12-26-2018, 12:51 AM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Sample attached here..........
Attached Files
File Type: xlsx Sample_data.xlsx (9.5 KB, 3 views)
Reply With Quote
  #4  
Old 12-26-2018, 01:14 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Do you mean like shown in the attached?
Attached Files
File Type: xlsx Concatenate.xlsx (10.1 KB, 6 views)
Reply With Quote
  #5  
Old 12-26-2018, 02:30 AM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Thanks for the reply...

No.

Values in the cell which is in same cell and comma separated is dynamic, the values are changing one or more items with comma separated.

for example sometimes,

Cell A1 contains like -----> S1,J1,K2,SD1....etc (there is no count)

required output at any cell like ------><categ no = "S1"/><categ no = "J1"/><categ no = "K2"/><categ no = "SD1"/> etc…
Reply With Quote
  #6  
Old 12-26-2018, 02:45 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Not sure what you mean.
Reply With Quote
  #7  
Old 12-26-2018, 02:50 AM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
Not sure what you mean.
This is what i need exactly.

The values in the "INPUT" column is dynamic which needs to be converted as output column.....

Please let me know if not clear.
Attached Files
File Type: xlsx Sample_data.xlsx (9.6 KB, 3 views)
Reply With Quote
  #8  
Old 12-26-2018, 03:24 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Can you accept to have as many helper cells (for each row) as there are categ nos?
Reply With Quote
  #9  
Old 12-26-2018, 03:28 AM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Hi XOR,

Each comma separated value in helper cell needs to be converted as each categ no.

I know its very strange to apply excel formula.

There is any other VBA code also would be fine.
Reply With Quote
  #10  
Old 12-26-2018, 03:40 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Then I think you should ask in the Excel Programming group.
Reply With Quote
  #11  
Old 12-26-2018, 03:51 AM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
Then I think you should ask in the Excel Programming group.
Do you have any other suggestions to using LEFT,RIGHT,MID,LEN,CONCATENATE,AND,OR,IF functions with "categ no" string.

I am just thinking to use this functions for this thread.....
Reply With Quote
  #12  
Old 12-26-2018, 08:15 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Don't know if this can be of any help.

To be able to use this workbook you must have access to the function TEXTJOIN which is available in Office 365. I am, not sure if it is in Excel 2016.
Attached Files
File Type: xlsx Category.xlsx (13.5 KB, 5 views)
Reply With Quote
  #13  
Old 12-26-2018, 09:32 PM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Thanks for reply.

Sometimes the input values separated by comma having more than 50 item included.

In this case not able to split helper cells separately, because of scrolling of more columns.

Anyway i can try with excel programming.
Reply With Quote
  #14  
Old 12-26-2018, 10:08 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Why not?
Why do you have to look at the helper cells at all. You just have to extend the helper cells (and the formulas) to 50 or whatever columns.

By the way - always best to give full details from the start (50 items).
Reply With Quote
  #15  
Old 12-26-2018, 10:23 PM
ganesang ganesang is offline Windows XP Office 2016
Competent Performer
 
Join Date: Jul 2018
Posts: 125
ganesang is on a distinguished road
Default

Hi XOR

I need the values in same cell instead of separated by more columns.

We're not sure either which column's value is big or small to expand and add the tags in to the more columns.

That's why i need the output in each respective same cell....
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help need for concatenate function issue ganesang Excel 5 11-24-2018 12:27 AM
RIGHT Function With Concatenate (?) VBA-Need Help with Macro rsrasc Excel Programming 4 08-22-2018 12:17 PM
Concatenate b16 Excel 1 01-09-2017 04:51 PM
Concatenate function for displaying numbers in words officeboy09 Excel 12 10-30-2013 08:08 PM
If, move and concatenate in VBA devcon Excel Programming 0 07-04-2011 12:44 AM


All times are GMT -7. The time now is 07:38 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft