Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Opposite Of Concatenate

I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Fucntion That Opposite Of CONCATENATE
is there a fucntion that does the opposite of CONCATENATE?

i have this

-EUR 1.00
EUR 2.00

i want to remove the "EUR " to end up with


View Replies!   View Related
Opposite Of Sumproduct
what is the opposite of sumproduct? I want to divide the quantity in column A by the opposing value in Column B to get the answer in B5. I made this example simple but it would be used on a much larger scale. if there is a function that would divide and sum ?


View Replies!   View Related
Counting Opposite Signs Between 2 Arrays
I have two columns with values of either 1 or -1. I'm trying to find a formula that I can write in one cell that counts the # of times column A and column B have opposite signs.

View Replies!   View Related
Duplicate Every Nth Cell In Opposite Column
I have a worksheet, example data below, it has 150+ columns and varying amounts of rows 1 -> 2500+ per column. The row data is as follows:

Row 1: Has title of worksheet
Row 2: Date of the column
Row 3: Total of completed for the day
Row 4 - n: Serial number

I need to intsert a new column to the right of each column and then populate it with the date at the top of the column from the left, I need the new date value to go down until it reaches the end of the data in the left column. So effectivly each serial number will have date in the column to its right.

There are no blanks in the rows, or blank columns.

Example of existing:



100621090 5/04/2007
104436159 5/04/2007
104876402 5/04/2007
462933606 5/04/2007

**Note date is in second column and taken from top of first column. Does not need to update if date is changed.

View Replies!   View Related
Change Value Based On Opposite Cell
In Column A I have a number of differnet country codes and for 2 of them I want to change the value in Column B. For Example if cell A1 is = to "SHA" I want cell B2 to be "SHANGHAI". If cell A1= to "SGN" I want B1 to be = to "HO CHI MINH". If cell A1 is = to anything else I want the value to B2 to remain unchanged.

View Replies!   View Related
Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.


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

View Replies!   View Related
Deleting Rows Where Values Are Opposite & Equal
I have a spreadsheeet with values in columns F:K from row 10 onwards

I need VBA code that will delete the rows where there are values opposite and equal in columns F:K from row 10 owards for eg if F10 is 10000 and K10 is -10000, F 25 is 150000 and G25 is -10000, G29 is -175000 and H29 is 175000, then rowa 10,25 & 29 must be deleted

View Replies!   View Related
Copy Formula Down To 1st Empty Row In Opposite Column
code to copy a cell formula all the way down the same column to the cell where its next immediate row is completely empty.

View Replies!   View Related
Summing Up Opposite Numbers In A Column Based On Criteria
Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.

I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.

For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.

View Replies!   View Related
Delete Opposite Rows Based On Multiple Criteria
I have a Macro that I use daily on a spreadsheet I get with raw line-item transaction data on it. Basically, it deletes columns I don't need to look at, applies column headers, deletes certain rows that I don't need to look at, applies an auto-filter and filters to the first group of data I need to look at, and puts a subtotal on a couple of columns at the top so I can see totals on filtered results.

I need an additional function in this Macro. At some point in the middle of the actions above, I need to evaluate all rows and delete those that are 'opposites' based on a couple of criteria. Basically, I need to delete any transactions that have been refunded in their entirety, because for my purposes those are zeroed out and I don't want to see them....

View Replies!   View Related
Copy Cells To Worksheets With Worksheet Name In Opposite Cell
I am developing a Workbook where I import data to one worksheet and then disseminate it to other worksheets. I am not sure if I am overthinking the process, but I am having difficulty writing the macro and getting it to run through the entire list of rows.

There are 4 Columns in the one worksheet, named "Update":
Column A supplies the name of the worksheet that will be updated
Columns B-D contain the data that will update the sheets.

The process that I am trying to write the macro for is a follows:
1. Select/copy the range, starting with B1:D1 on "Update"
2. Navigate to the appropriate worksheet to be update, determined by A1
3. Identify the first blank row, starting with A5
4. Paste the data B1:D1 to A5:C5 (or next available line)
5. Navigate back to "Update" and begin the process again with the next row B2:D2

This process should continue until there is no value in Column B on the "Update" worksheet.

View Replies!   View Related
Delete Rows If Opposite Column Cell Matches
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!

Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
End If.........................................

View Replies!   View Related
Lookup Value In A Cell Based On Value In Opposite Cell
I need to find the value in cell range A:10 A:242 based on
the search criteria found in G:10 H:242

View Replies!   View Related
If Cell Is BLANK Then Look At Opposite Column Cell
I have a column B which has the text "" in some cells where there is no value. I want a macro that will find a "" and if a cell has then I want the cell to equal to column A
for example ...

View Replies!   View Related
Concatenate With If Criteria
I created a concatenate on a co-worker's spreadsheet so I could utilize the data, and it works fine. But then I realized when reviewing the results, that there are MANY instances where not all of the fields that the concatenation pulls from contain data. I verified the source, and found out that any of these fields may or may not contain data. Now I need to account for this within my formula.

The cells being concatenated are in columns G-K, my first formula (in cell L4) is:

=IF($A4=3,CONCATENATE(G4,"; ",H4,"; ",I4,"; ",J4,"; ",K4),"")

Now I am using a "; " to separate between each cell's text. I need to adjust this formula to account for the fact that any of those cells MAY not contain data. And if it does not, I do not want the separator ("; " to show), because otherwise, I get entries that display like this:

up to 10.; ; 1.NS.1.1

If necessary, I can easily place my separator in a specific cell (M1 would be good) and access it from there rather than the way I initially wrote the formula.

View Replies!   View Related
Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.

If I enter
instead of showing the strings located in A2 and B2 it displays
in the cell.

I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.

View Replies!   View Related
Concatenate A List
I have a list containing 3 the abbreviations of states in A1:A3 (one in each cell), and I would like to concatenate the list in cell B1 to display the list like this: "CA, TX, FL". At times, the list will only have one state (always in A1), but at other times it may have 2 or 3.

I tried: =IF(ISTEXT(A),A1&", "&A2&", "&A3,A1)

But, if A2 does not list a state then it returns "#value!"; and if A2 lists a state but A3 does not then it returns only the value in A1.

What can I do to get it to list all states listed in column A whether there are 1, 2, or 3 states listed?

View Replies!   View Related
Concatenate Column With Row Value
I have created vbscript that references a particular column and row. This cell is referred to many times in the scripting but the row may change from time to time. I would like to know how I can define the row value at the top and reference this row within my vbscript.

Dim row As Integer
row = 1

'vb script coding referring to cell
ws.Range("B&row").Copy Destination:= Sheets("Summary").Range("B1")
ws.Range("E&row").Copy Destination:=Sheets("Summary").Range("B16")
ws.Range("F&row").Copy Destination:=Sheets("Summary").Range("B17")

I tried to concatenate the row to the column but it doesn't seem to work. When I would like to change the row value I would like to only make the change where row is defined rather than within each line of coding.

View Replies!   View Related
Reverse To Concatenate
a way in which to split the following line into say 5 separate pieces

George Julius Aaronson (81) 12th Feb 1945 D 9th Jan 1948.jpg
George Julius Aaronson
12th Feb 1945
9th Jan 1948

so that they are in 5 separate cells on a row. just to make things harder no name in the first colum has the same number of forenames or the same number of digits.there are possibly 600+ of these lines of which the next one is below
(Hanna Auerbach (73) 2nd Oct 1949 D 13th Feb 1955.jpg)

View Replies!   View Related
From cell A1 to A10 I have the figure 5412587
In cell B1 I want the figure in Cell A1 to concatenate with the figure one but in cell B2 instead of figure one with figure two etc. etc.
So B1 will be 54125871, B2 54125872, B3 54125873 etc. etc.
the formula would be Concatenate(A1&1). I want the "one" in this formula to be increasing so in B2 will be Concatenate(B1&2) et.c etc.

The problem is that I dont want to add an extra column to replace the 1, 2, 3 etc. in the formula.

View Replies!   View Related
I am using the concatenate function, however my date field, instead of maintaining its 08/07/07 state, changes to the annoying 5 digit 33935 number.

Even if I cut and paste it as special / values, it stays as the 5 digit number. Can this be avoided somehow?

View Replies!   View Related
Concatenate With If And Copy Then Sum
I'm trying to Concatenate E4 through E60 then Copy Column B,C,D,F,G to Single row then Sum H Through M. I've got My code Concatenating E But have not been able to get the rest.

Sub JoinCarts()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As String, i As Long, j As Long
Dim MyStr As String
Set ws1 = Sheets("Count Sheet Esman Greenhouse's")
Set ws2 = Sheets("Count Sheet Print")
j = 4
With ws1
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 4 To 60
If .Range("C" & i).Value = .Range("C" & i + 1).Value Then
If .Range("D" & i).Value = .Range("D" & i + 1).Value Then.............

View Replies!   View Related
Concatenate Command
i need to merge several columns together with a comma seperator. but there are some columns with only set of data in and sometimes 6 columns with data. so i end of with something looking like WC,,,,, or GAU,MAJ,CHEM,,, etc.
i have over 6000 lines so don't feel like cleaning up manually.

View Replies!   View Related
Concatenate Returns #VALUE!
I want to include the numeric value from D105 within this text, but my forumla returns #VALUE!

=CONCATENATE("FMV Lodging ",(D105)," ee's")

View Replies!   View Related
Automatic Concatenate
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".

View Replies!   View Related
Concatenate With Indirect + VBA
2 questions:

1. How can i put an Indirect function nested with concatenate?


2. How can i get the formula to adjust to new data range without manually filling down. Assume the formula starts CELL Q8

View Replies!   View Related
Concatenate And Bold
I have a simple concatenate in Excel...

=CONCATENATE(A1, ", ", A2)

Which produces:

This is, my text

Now, I want 'my text' i.e. the contents of A2 to be in bold.

I've played with the TEXT() function before, as per this page:

To get Excel to play nicely with date formats and stuff, however, my research has come to a dead end with Bold/Italic - with everyone suggesting that VBA is the only solution.

As I don't feel overly comfortable using VBA is there any other solution? (If not, is this a VBA 101 task or an advanced task?)

View Replies!   View Related
The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS?


View Replies!   View Related
Concatenate Cells With % In Them
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 Replies!   View Related
Concatenate The Dropdown
I am trying to get 2 rows in one drop down without having to create another combined row. im not sure what formula should i use. I have attached an example.

View Replies!   View Related
Concatenate With Delimiter
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 Replies!   View Related
Concatenate (two Different Cells Into One)
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 Replies!   View Related
VBA Concatenate Formula
I'm trying to use a macro to write a formula within a column of data.

Here's the

View Replies!   View Related
If / Concatenate Into One Cell
I have 6 columns:

A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2

I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).

So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")

Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".

View Replies!   View Related
Concatenate 2 Column
i trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc

with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?

View Replies!   View Related
File Name In A Concatenate
Can I put the file name into a concatenate formula?

What I've got is seats for lots of different settees that I want to show up on a cutting sheet as ie:
10 Chatsworth Seats
15 Abbey Seats

At current its is reading 'Seats' from the file Chatsworth but if I can add filename to my concatenate it will say "Chatsworth Seats"

View Replies!   View Related
Concatenate Function..
What function can get me the first letter from the first name and the whole last name together and then add Example: Hanry Jones =

View Replies!   View Related
Increment Using Concatenate
I want to write formula in cells(many cells!) using concatenate. The desired output:

In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F

Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.

View Replies!   View Related
Concatenate Columns
Item No amountinvoice no
2050377 14,870 105
5000085 120 1102
5000085 15,000 90197361
5000139 16,000 12346
5000237 31,000 56789
2050377 20,000 10111213
2050377 ($20,000)105

I have a spreadsheet with 3 columns. when I'm reimbursed money (see 2050377 for ($20,000) i'm trying to use VLOOKUP to search the spreadsheet and find out the invoice that relates to that reimbursement. but as you can see VLOOKUP finds the first incidence of 2050377 and gives me inv no 105 when it should be 10111213. my formula in C8 is =VLOOKUP (A8,$A$1:$C$8,3) but I need for the VLOOKUP to look at the item number as well as the amount. i've been told that I need to utilise concatenate, but I don't know how to do this.

View Replies!   View Related
Concatenate Paragraphs
What I am trying to do is to have the concatenated content of two different cells seperated into paragraps.

For example, if A1=Blue and B1=Red I want the content of C1 to look like

I use =A1&B1 in C1, but I'm missing something to split them.

View Replies!   View Related
IF THEN Concatenate AND Cut And Paste
I have imported .csv files into a workbook and there are instances where the data did not separate properly. When this happens, part of the text (an MCI circuit ID) that should be in column A, ends up in column B. This obviously moves the data that should be in B:I to C:J. I have the code below, which, when "*MCI*" is found in a cell in column B (and as extra validation), a "0" is found in column J, the text in column B is joined with the text in column A. This works fine. What I need is to also have it delete the circuit ID that still exists in column B and then cut the data in C:J and paste it in B:I.

If it matters, there could be as many as 25,000 rows.

Sub IfMCIexists()
Dim a As Long
Dim b As Long
Dim i As Long
Dim j As Long
Dim r As Long
Dim lr As Long

'Fancy report headings in rows 1 thru 7, so start at Row 8
r = 8

'Column in which text will be combined if conditions are met A (1)
a = 1

'Search Column B for condition one (if *MCI* exists)
b = 2

'Search Column J for condition two (if "0" exists)
j = 10

'Determine last row containing data
lr = Cells(65536, b).End(xlUp).Row

'Loop and concatenate if "*MCI*" is found in column B and "0" is found in column J

For i = 8 To lr
If Cells(r, b).Text Like "*MCI*" And Cells(r, j).Value = "0" Then Cells(r, a) = Cells(r, a) & Cells(r, b)
r = r + 1
Next i
End Sub

View Replies!   View Related
Concatenate Two Columns
writing a macro with following requirement

i need to display the third column with the concatinate values of column 1 and column 2

if A1 = Hi
B1 = All

then C1 should be Hi All

View Replies!   View Related
Concatenate Whole Column To Row
how to concatenate whole column to a single cell in Excel. I have 3605 cells to be concatenated to a single cell

View Replies!   View Related
Concatenate But Ignore Blanks
I'd like to concatenate two columns but ignore blanks.

I've attached the file that I'm working with.

I'd the format to be as shown in the sheet.

View Replies!   View Related
Concatenate Use On Chart Title
I'm using concatenate to name a chart title. My problem is that when I concatenate two dates I lose the date format. I'm after a title which looks like 06 December 2007 - 13 December 2007

I'm actually getting 39422 - 39429

The cells have been formatted in date format but lose this when concatenated.

Is there a sollution?

View Replies!   View Related
Concatenate & Indirect Logic
I need your help on the attached sample sheet. I have used the concatenate & indirect combination logic to get the desire comments as outcome , but my problem is that I have 300 rows & 30 sheets in a workbook with this logic , it is increasing the size of my file as I have a total of 30 sheets with same logic. I need it in every sheet. If there is any other alternative or solution.

View Replies!   View Related
Can't Get CONCATENATE To Work. No Results.
I have built a formula using CONCATENATE. (Built in G2) When I copy and paste it into a empty cell (E2), one that is formatted as General and is not locked, as text or value or a formula I get the same results. It always shows up as text. I can't get the formula to function and show the results in the cell (E2). Yet, when I singularly build the formula in the cell it works fine. But the moving (cut and paste) from one cell to the other doesn't work.
The concatenated formula is: =CONCATENATE("=LEFT(",G2,",",H2,")") The result is: =LEFT(B2,3) . These results look fine, yet only when built in the cell using Excel's Insert Function builder or doing it by hand it works fine. It returns the first 3 numbers/text from the left side of cell B2. But the data that should result from the concatenated formula and pasted in to the cell (E2) is not showing up as needed. Any help would be appreciated.

View Replies!   View Related
Concatenate Formula Amendment
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.
Is this possible? I've tried to use the formula below with no luck,


View Replies!   View Related
i need to create a macro that is dynamic.

i have attached a example on here.

My main idea is that i have to select the rows/column to compare that they are the same for the selected column then concatenate and merge.

In my example, i wan Column B be like Column D... but in the end i wan it to be like Column F if they are the same.

View Replies!   View Related
Fixed Width With CONCATENATE
Excel 2007

I want to CONCATENATE 3 columns

column 1 will have names
Columns 2 and 3 will have numbers
All will be of different lengths

Is there any way to set a fixed width when I use the CONCATENATE funstion so everything looks more uniform (left justified)

View Replies!   View Related
Copyright 2005-08, All rights reserved