It was being able to concatenate a list of names with commas between names. The resolution was: =Substitute(Trim(E13&" "&E14&" "&E15&" "&E16&" "&E17&" "&E18&" "&E19)," ",",")
This worked great until my boss said "I want first and last names. Now someone whose name makes the list is shown as john, smith.
I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.
I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)
I'm trying to split a cell with 2 different delimiters.
As example
A1 10'48"
to
B1 10
C1 48
I have about 10.000 entries like those. And i think a macro should be the most suitable for it.
But I'm not experienced enough to make it work.
I'm trying to write code to join two columns of data, and then join all of those into one cell.CA
California
DE
Delaware
CT
Connecticut
I want to be able to run the macro and have it put {"CA":"California","DE":"Delaware","CT":"Connecticut"} into cell C1.
I was thinking that this would have to be a two part macro. First, it would take each specific row and join CA and California and add the colon, quotation marks, and the comma, and put it Column C. Then, after it's gone through every row, it would join every cell in column C together.
This is what I've written so far:
VB:
Sub test1()
Dim r As Range
Dim lr As Integer
[Code]....
Now I need to figure out the second part. How do I then take all of the join cells in column C and join them together, adding the curly braces at the beginning and the end, and removing the comma from the very last row?
I need to import a number of log files into a spreadsheet, each log will go into a separate tab. The logs are text files (.log) that have the same structure but slightly differ from each other.
For example all the logs will have many lines of data all starting with a date and then a number of delimited fields. In some logs the delimiter is a coma, in others is a unique charachter such as "{" or "@". Also the delimited fields are not a given quantity, they can vary too from log to log. How can I write a macro that understands what delimiter needs to be used and then imports all the file accordingly into one spreadsheet (in separate tabs)?
I tried something with the macro recorder and browsed for directions on the internet but really am clueless on this one. I am using windows 2000, and referenced Windows Scripting Runtime.
Having some problems splitting data within a single column into several using VBA rather than a Formula. (I have been able to get working using a formula). I have found a few similar theads but nothing i seem to be able to convert with my some what limited skills
My data is always in a sheet called "Release Data" in column A, the number of rows varies daily. The data is always extracted with the delimiters in the same position E.G.
NNNNNN_AAA_Variable length txt
I would like to split the data into columns I, J and K.
I have a spreadsheet which performs certain operations as long as I insert 3 values in 3 cells. The results for different scenarios are shown within the same sheet. excample:
I insert
3,52,051,22
then many calculations are performed automatically and the result are shown in other cells in the same sheet.
My goal is to be able to copy/paste ca 8-10 strings of information at the same time. All of them at the same time, instead of only one at the time. Also, there will be a textstring and some digits I don’t need along with the copy/pasted info.Ex:
Name Time Date X1 X2 X3
Lou 22.11 01.03.2006 3,5 2,05 9,72
Lie 12.01 01.03.2006 2,5 2,65 1,22
Tom 17.09 01.03.2006 4,5 8,09 3,28
etc.....................................
I have a column full of text with most data separated by commas, except sometimes between the commas there is a string, marked by ' ' , which itself contains commas.
For example: 45,'im a string, look at me',67,43,5,'im another string, look at me',78
I try to make excel put all the data into columns, so 'im a string, look at me' will have its own column, instead of being split into two columns. I tried telling excel that the ' character marks strings, but it just removed the apostrophes and kept splitting any string that contained a comma. I tried to use a special delimiter,' but excel didn't allow me to. Does someone have a macro that will do the text to columns for me, or is this possible to do with the regular text to columns feature of excel?
I have a set of data in column A that looks like this:
White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|
The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.
The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.
I am trying to consolidate an .835 raw text file that is full of receipts and payments into rows by specific values/characters. Once opened within notepad/text format, I figured out that the ~CLP is the referencing value/character used to break up each individual payment.
I need to somehow use the ~CLP as the value(s) to sort the text into new rows, with each row pre-leading with the ~CLP.
For example:
~CLP*442.24*DTM*B6*PR~CLP*124.32...etc
New Row format needed:
~CLP*442.24*DTM*B6*PR
~CLP*124.32
I need to "convert" Excel table to TXT or CSV file, but delimiters have to be ";" and text must be in quotation marks "".
View 1 Replies View RelatedI have a column within a spreadsheet that has data separated by 2 delimiters, a "" and a "/". (This data has been extracted from a linux-based file.)
For example: "1100789/3200899/6xlm-sgt-455-0987"
The items of data are from a bill of material (parts explosion) and the number preceding the "" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)
I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.
for example:
1 100789
3 200899
6 xlm-sgt-455-0987
Is this possible with the data tools in a spreadsheet, or will I need to write a macro?
I would like to extract multiple numbers from a single cell. The cells contain -, /, and blank spaces are delimiters. An example is 4 - 3 1/4 and because of the way they use that number, it really means 4 inches and 3.25 fifths of an inch (3.25/5 inches) which is weird. I want to go through each cell and store the 4, 3, and 1/4 as variables so I can then manipulate to convert them to the proper inch value (I can perform the conversion code, I just need to know how to set those variables in the cell). And I would like it to automatically do it when a cell has been changed. formatting to maintain overall table width dimensions so if you know anything about that,
both cells are blank
4 - 3 1/4
cell value is enter in on the left
4 - 3 1/4
4.65
converted cell value appears on the left
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
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 ,
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
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 3 Replies View RelatedI'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..
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 7 Replies View RelatedI 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.
I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:
1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8
I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008
Easy enough! Now adding the following is the problem:
1. Cell A2= 12345 Cell B2= 67 Cell C2= 89
I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089
Now D2 has one too many characters from C2
I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.
Is there a way to use a variable with the concatenate or am I using the wrong formula period ??
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 7 Replies View RelatedI'm trying to use a macro to write a formula within a column of data.
Here's the
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 ?
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
=CONCATENATE(A2,B2)
instead of showing the strings located in A2 and B2 it displays
=CONCATENATE(A2,B2)
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.
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 "".
What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com
View 2 Replies View RelatedI have this code
Code:
Selection.FormulaR1C1Local = "=INDIRECT(CONCATENATE(""["",R4C26,""]Sheet1!"",RC))"
Which creates this in the excel cell:
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!",D4))
But for some reason, it returns a 0. However, when I change the code in the excel cell to this
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!D4"))
It works. But I need to be able to change D4 in the macro, so doing it that way won't work for me.
I have the below set of data for example,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
I need the output as,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.
Currently I am trying to concatenate two arrays stockotherarray and stockfittingsarray to create stockarraynew
Below is my code, I keep receiving a compile error.
Sub stockarraynew()
stockarraynew() = Split(Join(stockotherarray & Join(stockfittingsarray))
End Function