Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-26-2018, 12:07 AM
ganesang ganesang is offline Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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 Concatenate - function help Windows 7 64bit Concatenate - function help Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please post a sample sheet with some data and expected results
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 12-26-2018, 12:51 AM
ganesang ganesang is offline Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Sample attached here..........
Attached Files
File Type: xlsx Sample_data.xlsx (9.5 KB, 9 views)
Reply With Quote
  #4  
Old 12-26-2018, 01:14 AM
xor xor is offline Concatenate - function help Windows 10 Concatenate - function help Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,094
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Do you mean like shown in the attached?
Attached Files
File Type: xlsx Concatenate.xlsx (10.1 KB, 12 views)
Reply With Quote
  #5  
Old 12-26-2018, 02:30 AM
ganesang ganesang is offline Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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 Concatenate - function help Windows 10 Concatenate - function help Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,094
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Not sure what you mean.
Reply With Quote
  #7  
Old 12-26-2018, 02:50 AM
ganesang ganesang is offline Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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, 9 views)
Reply With Quote
  #8  
Old 12-26-2018, 03:24 AM
xor xor is offline Concatenate - function help Windows 10 Concatenate - function help Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,094
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
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 Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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 Concatenate - function help Windows 10 Concatenate - function help Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,094
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
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 Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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 Concatenate - function help Windows 10 Concatenate - function help Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,094
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
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, 11 views)
Reply With Quote
  #13  
Old 12-26-2018, 09:32 PM
ganesang ganesang is offline Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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 Concatenate - function help Windows 10 Concatenate - function help Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,094
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
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 Concatenate - function help Windows XP Concatenate - function help Office 2016
Competent Performer
Concatenate - function help
 
Join Date: Jul 2018
Posts: 171
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
Concatenate - function help Concatenate function for displaying numbers in words officeboy09 Excel 13 08-09-2021 12:59 PM
Concatenate - function help Help need for concatenate function issue ganesang Excel 5 11-24-2018 12:27 AM
Concatenate - function help RIGHT Function With Concatenate (?) VBA-Need Help with Macro rsrasc Excel Programming 4 08-22-2018 12:17 PM
Concatenate - function help Concatenate b16 Excel 1 01-09-2017 04:51 PM
If, move and concatenate in VBA devcon Excel Programming 0 07-04-2011 12:44 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft