Concatenate Cells Using Delimiter

Mar 7, 2008

I receive multiple worksheets containing transaction data with multiple numbers of row and columns. The data columns contain varying types of information like: price, quantity, location1, location2, loading dock, transaction date, buy/sell, purchaser, seller, currency unit, etc.

I have a need to concatenate into a single cell in a blank column, data from a number of cells in each row -- sometimes two cells, sometimes more. In some instances, I'd like to concatenate data for location1 and location2 with a delimiter like a semicolon-and-a-space to create a cell with: "location1; location2" Or in another instance, I'd like to concatenate multiple cells in a row, like: "location1; price; quantity; transaction date", etc.

I'm looking to create a macro that, with the cursor in a blank cell in a blank row, would open an input box, ask a user to select with the cursor the cells in the row to concatenate and then concatenate them into the blank cell in the row.

Ideally, the macro would populate the entire blank column with the concatenated data for the group of selected cells (from the same columns as in the row where the cells to be concatenated were selected) for each row where there is data. It is also possible that some cells in some rows of the selected columns would contain no data. In that case, I'd be looking to return just the delimiter, like: "location1; ; quantity; transaction date"

Right now, with no macro, I do it by hand like this:

To concatenate into cell I6, the contents of cells A6, B6, G6 and H6, I do a "=CONCATENATE(A6,"; ",B6,"; ",G6,"; ",H6)" in blank cell I6, but I'm looking for a way to do it fast in a macro that would fill in all of Column I.

View 5 Replies


ADVERTISEMENT

Concatenate With Delimiter

Jan 31, 2009

to concatenate across columns, say J through Q, (some of which are blank) and have the results delimited by a comma without returning anything for columns that might be blank. So R660 would read "0263B001A,FX9,FX-9,FX10,FX-10" and R669 would ...

View 9 Replies View Related

Concatenate Multiple Rows In A Column Using Delimiter Based On Changing Value

Aug 16, 2013

I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.

Below is an example with the solution I am attempting to produce.

Sample Table

Name
Dept

Abbruzzese,James L
188100

[Code] ......

Output

Name
Dept

Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708

Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435

View 2 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two
one

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

Split Cells Into 2. Space As Delimiter

Oct 18, 2006

I recently got this task by my work employer to transfer information from several non-organized Excel-documents into one, complete and organized one.

The thing I have to organize right now is a column with thousands of zipcodes and zones in one single cell, only separated by spaces, like this:

Zipcode Zone
115 54 Gothenburg
443 67 Skane

What I want to do is instead of having to go through 24h of braindead control/paste is to use some easy and fast function or script that could pull out the last piece of string of information in every cell in that column into its own separate cell.

Although I am using Open Office Calc/Gnumeric on Ubuntu Dapper, if a script is necessary and only work with Excel on Windows/Mac, that is no big problem for me to solve. So please, any suggestion is welcome.

I will attatch two examples for you to look at, to get the whole picture.

View 8 Replies View Related

Keywords As Delimiter To Split Cells

Jul 1, 2008

I've got a column of data containing addresses. What I want to do is split the column after certain keywords such as "street", "road", "close" etc and move any words that come after these in the cell into the next cell to the right.

For example, in one cell I might have "54 Burberry Road Finsbury London"... what I want to do is have part of the text remain in the original cell i.e. "54 Burberry Road" and the rest, "Finsbury London", moved to the cell on the right.

Not all cells in the column will match this format so if it doesn't find a keyword, nothing will occur and it will move to the next cell down etc. until it reaches the end of the column.

There are no delimiters (apart from the spaces) between the words.

I haven't figured out all of the keywords that I will need to search for yet but I should be able to extrapolate from something fairly generic.

View 5 Replies View Related

Splitting Continuous Text Into Two Cells Using Uppercase Letter As Delimiter

Nov 12, 2009

I have names that look like this:

SmithJohn and i want to seperate into two cells so it looks like

Cell 1: Smith
Cell 2: John

View 9 Replies View Related

Concatenate (two Different Cells Into One)

Dec 11, 2008

I'm having a rather difficult time getting this one.

I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.

A picture is worth a thousand..

View 4 Replies View Related

Concatenate Several Cells On Tab One

Apr 11, 2013

On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date. Any way to lock the date format for this idea?

View 7 Replies View Related

Concatenate Cells With % In Them

Apr 27, 2007

I have 7 consecutive cells which contain a %
e.g. A1 120%, B1 35% etc

I am trying to put them into one cell using concatenate
but am getting 1.20.3529411764705880.705882352941177

I would also like to separate them by comma,

View 9 Replies View Related

Format: Concatenate Value Of The Several Cells At One

Apr 30, 2009

I concatenate value of the several cells at one. (A1&"/"B1&"/"&C1 and etc. The length of characters may be different and I want it be looking neat by adding spaces. Something like that:

400/ 25/ 30
40/250/300

View 5 Replies View Related

IF OR And CONCATENATE To Read The Cells

Jun 1, 2009

I've been trying to use IF OR and CONCATENATE to read 4 cells and give me a list of parts needed based on the data loaded in the 4 cells. I attached an example showing.

View 2 Replies View Related

Concatenate A Range Of Cells (A + B + C = ABC)

Sep 29, 2009

I know how to do the "normal" type of concatenation (concatenating?), but I was wondering if there was a way to concatenate a range of cells.

If my first three cells look like this:
A1 = Adam
A2 = Bob
A3 = Chris

I'd like cell A4 to say "AdamBobChris". I'm not worried about punctuation at this point. I thought I could just do =concatenante(A1:A3), but that's not working.

View 4 Replies View Related

Concatenate The Cells On Condition?

Apr 23, 2014

I have a Requirement where we need to concatenate the cells based on conditions

i have the attachment for the reference . please download the file from the link.

View 1 Replies View Related

Using Function To Concatenate Cells

Jan 29, 2009

I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).

View 2 Replies View Related

Concatenate Cells And Add Text

Feb 6, 2009

I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.

Example:

Assume row 1 and columns A, B, C, D.

496 | 6d7 | Minor | Ø.375 +/-.010

What I need this to read as one long string:

#496, 6d7, Minor, Ø.375 +/-.010

Adding the text in Red.

Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.

View 5 Replies View Related

Using For Loop To Concatenate Cells

Apr 14, 2014

I'm trying to concatenate a few cells but seem to be missing the mark with my code. show me where I went wrong.

Code:

Dim counter As Long
Dim b As Range
Dim e As Range
Dim g As Range
Range("E1").Select 'Set the range for the loop
Selection.End(xlDown).Select

[code]....

View 5 Replies View Related

Can I CONCATENATE Blank Cells

Jul 17, 2007

I have the following formula in cell N4:

=CONCATENATE(O4,CHAR(10),"EBC Briefing: "&TEXT(P4,"dd-mmm-yy"),CHAR(10),"Site: ",Q4)

As long as 04:Q4 are ALL non-blank cells, the formula works. However, if ANY of the cells are blank, it displays the green rectangle in N4, but it doesn't offer the Trace option.

View 9 Replies View Related

Concatenate Multiple Cells

Nov 25, 2009

I have a macro to concatenate multiple cells but unfortunately it is not giving the result,

It has to concatenate below mentioned cells and put the result in column N

Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
Range("N1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

View 9 Replies View Related

Concatenate & Copy Cells

Jan 6, 2007

I have a spreadsheet of inventory items that I am trying to prepare to import into Quickbooks. There are items in this sheet that are subitems of a parent item. The subitems have the same ID number (col. C in the attached sample), different quantities and different descriptions than the parent.

I need to to concatenate the Item Lookup Code (col. D in example) and the description, separated by a colon. Then I need to copy everything else in the parent record, except quantity and description, into the child records. There are close to 20,000 items in this sheet.

View 4 Replies View Related

Concatenate Cells With Text

Mar 22, 2007

I want agglutinate some text values in the same cell. Something like that:

A1="John"
A2="Elton"
A3="Mark"
A4="AND"


B1=A1+A2+A3 =(John Elton Mark)
B2= A1 +A4 +A2 + A4 +A3 = (John AND Elton AND Mark)

View 3 Replies View Related

Concatenate Several Cells If Not Blank

Jun 8, 2007

I am attempting to concatentate several cells (10 to be exact). I only want to include cells that are not blank in the output. I searched the topics and found a similar problem. I attempted to modify that solution to fit my needs but I obviously do not quite understand exactly how it worked. I have attached a sample. The expected results are that if in row 1
columns a through j have any value they will then be concatenated or joined in some other fashiion seperated by a comma. I only want to include non blank values. I have placed the code that I attemted to use in this post.

=IF( COUNTBLANK(GL41:GU44)=0,GL41&", "&GM41&", "&GN41&", "&GO41&", "&GP41&", "&GQ41&", "&GR41&", "&GS41&", "&GT41&", "&GU41,IF(ISBLANK(GU41),""&GL41,GL41&", "&GU41))

Some of these cells are merged as indicated in the range of countblank().

View 6 Replies View Related

Concatenate Each Row To Cells In Column

Aug 8, 2008

I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set. I believe the code below is close, but I am receiving this error

Run-time error '91':
Object variable or with block variable not set

Debug points to this line

rngComment = . Cells(intCounter, 5)

I'm not sure what I am doing wrong.

Option Explicit.........................

View 7 Replies View Related

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C

View 5 Replies View Related

Concatenate Variable Number Of Cells?

Sep 15, 2014

I have two columns - A and B. In column A there are blank cells and filled cells. There is a blank cell and after it - a variable number of filled cells, then a blank cell again and a variable number of filled cells. I am trying to concatenate the filled cells from column A into a single cell in column B, which is corresponding to the blank cell in column A. Values should be comma delimited.

For example: A2-blank; A3-27; A4-63; A5-blank; A6-31; A7-blank .. and in column B should be B2-27,63; B3-blank;B4-blank; B5-31,B6-blanc and etc.

View 5 Replies View Related

Concatenate Group Of Cells If They Contain Certain Text

Jul 28, 2014

I am trying to figure out how to Concatenate a group of cells if they contain a certain text. Basically I have a list of Architects in column A and the account name in column B... and I want to say Concatenate column A, IF column B=Specific Account (ex: Coca-Cola). The list will be added to frequently so the formula must refer to the entire columns, not just a certain range.

As of right now, only the first architect associated with each account will appear. I have attached a workbook with the example..

example.xlsx‎

View 6 Replies View Related

Concatenate Formula (merge Cells)

Oct 14, 2009

I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.

View 4 Replies View Related

Concatenate Cells Address Style

Dec 30, 2009

How can I concatenate 2 cells address style? I'm trying to merge 2 cells into one, so that one is under the other in the same cell.

View 5 Replies View Related

Concatenate Cells And Remove Duplicates

Jun 23, 2014

I currently have a spreadsheet that contains about 1700 lines of data related to ~400 different clients which I am looking to consolidate.

The data is currently in this format (a set like this repeated ~400 times with anywhere between 3 and 7 services):

Company ID #
Company Name
Service1

Company ID #
Company Name
Service2

Company ID #
Company Name
Service3

I would like to have the data in this format (one row per company with all of the services in the third column concatenated into a single cell, all while removing the duplicate rows.)

Company ID #
Company Name
Service1, Service2, Service3

How to create the code (or describe the process) necessary to do this?

View 5 Replies View Related

Concatenate Cells And Delete Duplicates

Apr 1, 2009

I have an excel 2007 spreadsheet that lists
items on multiple rows for the same customer.

Each sheet will list anywhere from 25 to 100 individual customers.

I need to identify the duplicates, concatenate
the "Items" to a single cell on the first row, and
delete the duplicate rows. eg:

Cust #| Item
1 | A
1 | B
1 | C
2 | B
2 | E
3 | A
3 | C
3 | E

The result should look like:

Cust# | New Item
1 | A, B, C
2 | B, E
3 | A, C, E

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved