Search Comma Delimited String

Dec 1, 2009

I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.

For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.

I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?

View 9 Replies


ADVERTISEMENT

Create Individual Comma Delimited Cells From Delimited Cell

May 30, 2008

I need to create a comma delimited list based on variable start and end values for each row.

StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008

I'm not a VBA expert, or I would have created a Do While or For Each loop.

View 4 Replies View Related

SUM Of Values In A Comma Delimited Cell

Jan 13, 2010

Is there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.

View 14 Replies View Related

Sort With Comma Delimited Numbers

Jul 7, 2014

make a macro wich it's going to sort comma delimited number in the correct order.. see attached file to get the wanted reult..

View 5 Replies View Related

Saving To The CSV (comma Delimited) File

Dec 1, 2006

I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess

What it looks like NOW
9057926500

What is looks like after save

9.06E+09

If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.

View 9 Replies View Related

Text File But Comma Delimited

Sep 28, 2007

In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows.
I would like to export the first three columns only (Column A, B and C). The TXT file should have the following:
First line: "This file was exported from Excel"
Second line: The value of cell B4 of the worksheet
Third line: The value of cell B5 of the worksheet
Fourth line: Today's date ( as 27/Sep/2007)
Fifth line: No entries (Empty row)
Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value.
Seventh line: Cell A13 value, cell B13 value and cell c13 value.
etc, until the end or selected amount of rows.
The values of the different columns should be separated by commas
Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789)
Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890)

The text file therefor would look as follows:

This file was exported from Excel.
Company ABC
On this continent
27/Sep/2007

123.4567, 23.7654, 123.4567
1234.5678, 123.4567, 987.6543
12345.6789, 6.0000, 2.9876

I tried to adapt jindon's code but no luck.
I am currently doing it the long way.
='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12
This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.

View 9 Replies View Related

Data In Column A To Comma Delimited List

May 31, 2012

excel spreadsheet that has data in column A like in the attached example spreadsheet. How can I make this into a comma delimited list? So something like this...

food
drinks
home
shelter
ocean
water

to this ---> food, drinks, home, shelter, ocean, water

View 5 Replies View Related

Splitting Comma Delimited Numbers Into New Columns

Apr 20, 2009

I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View 12 Replies View Related

Open All Txt Files (delimited Tab Comma ) In A Folder

Aug 11, 2009

I have around 30 files each quarter which I need to convert from a text file to an excel spreadsheet. I am a beginner with VBA and am looking for generic code I can use for a macro to:

1. open a text file from a folder
2. delimited/tab/comma
3. format columns H, O, and AH into dates
4. 'leave a spot for me to insert my code to manipulate the data'
5. save the file as an excel spreadsheet to a folder with the same filename
6. loop to perform this task to all files in a folder and stop after the last file.

The dilemna I am having is that my text files do not have a suffix ".txt" after them.

They just have the file name ***MMDDYYYY. There are always 3 initials at the beginning which change for each file i.e. ABC06302009.

The date remains the same for the given quarter, i.e. ***06302009. Next quarter I will have to do this same thing for all files ***09302009.

View 9 Replies View Related

Separate Comma-delimited Values To Rows

Oct 22, 2009

I have an excel sheet I create with lots of inventory items distributed amongst several locations. The first column is a location field where several 1-2 digit location codes are seperated by commas. I would love to get the macro to copy each row, once for each location code, onto a new tab as a new longer list. Because the amount of data per row varies, I need the entire row to be copied to the new tab....

View 10 Replies View Related

Import Comma Delimited Info In A Column

Mar 4, 2006

I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.

View 10 Replies View Related

Convert Comma Delimited Cell To An Array

Feb 15, 2010

{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))}
I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.


I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.


{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.

View 9 Replies View Related

Convert A Column Into Comma Delimited List

Oct 27, 2006

I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.

What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.

Example:

The list (starting in A1) looks like this:
Dog
Cat
House
Car
Boat
Mom
Dad

but I need:
Dog,Cat,House,Car,Boat,Mom,Dad

If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.

View 4 Replies View Related

Separate Comma Delimited Cells To New Rows

Mar 31, 2008

I have a 5K rows of data, each including a cell (AD) of multiple number values, separated by comma. I would like to create a new row for each unique number in column AD such that there remains only one unique value for every AD cell.

If possible, I would prefer an in-cell formula rather than a macro.

View 9 Replies View Related

Remove The Extra White Space Between Each Comma Delimited Digit

Oct 25, 2009

This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345

My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:

"A B C 12345"

So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?

View 3 Replies View Related

Transpose Column Into Unique Rows With Comma Delimited Values

Nov 4, 2013

I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was Need to transpose multiple comma separated values

I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:

GA-AG-00010-A-2013
Apache Hunting Club

709

36

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.

Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

[Code] ......

How to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.

View 9 Replies View Related

Pivot Table Labels Based On Comma Delimited Cell Values

Jan 25, 2010

"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.

The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."

I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!

View 2 Replies View Related

Macro To Save Excel Sheet As Comma Delimited Text File?

Apr 21, 2014

providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.

View 14 Replies View Related

Need Macro To Create Formulas And Split (Comma Delimited) Cells Into Rows

Mar 30, 2013

Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.

I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...

Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.

Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .

Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).

Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).

E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"

Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....

Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F

View 7 Replies View Related

Dropdown List From Comma Delimited List In Single Cell

May 9, 2014

Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.

View 3 Replies View Related

Covert Space Delimited String To Array

Apr 24, 2008

I have two columns of data, one for Position, and one for description.

The positions are concatenated and separated by spaces. E.g. C1 C2 C3 for each Description. I need to create a separate row for each position

So I need to convert:

Pos Decscription
C1 C2 C3 Cap 100n 10% X7R 0805

to:

Pos Decscription
C1 Cap 100n 10% X7R 0805
C2 Cap 100n 10% X7R 0805
C3 Cap 100n 10% X7R 0805

Or in other words split a space delimited string into an array, so that I can loop through the array, and add new rows

View 9 Replies View Related

Separate Space$ Delimited String Into Multiple Cells

Oct 13, 2009

I am trying to split a string into separate cells. I have managed to generate the formula for the description and first dlr value in the string but I am have trouble figuring out how to build functions for the rest of the string.

Example attached.

View 3 Replies View Related

Converting Pipe Delimited Data String Into Separate Columns Using VBA Macro

Mar 6, 2014

I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.

U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date

View 2 Replies View Related

Remove The Last Comma In The String

Jan 16, 2007

The below function concats a range of cells by csv. How can I get it to remove the last comma in the string when it's finished?

Function SpecialConcatenate(rnge As Range) As String
Dim r As Long, col As Integer

For c = 1 To rnge.Columns.Count
For r = 1 To rnge.Rows.Count
If rnge.Cells(r, c) "" Then
SpecialConcatenate = SpecialConcatenate & _
rnge.Cells(r, c).Value & ","
End If
Next r
Next c
End Function

View 9 Replies View Related

Split String Separated Comma

Oct 9, 2013

In filed I have couple of value separated by comma like below:

A1 header1
B1 header2
C1 header3

Audi
592035, 579733, 653749, 579735
20 000

If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:

A1 header1
B1 header2
C1 header3

Audi
592035
20 000

[Code] .....

I have:

Code:
Set sourceWb = ActiveWorkbook
Set ws = sourceWb.Worksheets(1)

Dim LastRow As Long
Dim MY_Split As Variant

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

[Code] ...........

View 1 Replies View Related

Insert Double-comma At End Of String

May 25, 2008

i'm a complete excel novice, but i think this should be pretty easy for you guys...

i just bought a new satnav system after 4 years with tomtom...

anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:

-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,

thing is, most of them don't have the 2 commas at the end and just end with the number, like this:

-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247

therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...

i'm guessing for anyone with excel experience, this should be pretty easy to solve...

View 9 Replies View Related

Comma Separated String Element Count

Aug 20, 2014

I need to count how many comma separated elements are in each cell. This is what I have so far,

[Code] .....

The error is Method 'Range" of object'.

View 9 Replies View Related

1,000's Of Rows To One Comma-delimted Text String

Mar 25, 2008

I have a worksheet with 17000 rows of words that end in commas in column A, i.e.
Column A
Row 1 = Word 1,
Row 2 = Words 2,
Row 3 - Word 3,
etc...

I need to get this into the following single string format...

Word 1,Word 2,Word 3,etc...

I want to ultimately save it as a .txt file.

View 9 Replies View Related

Split Words In Comma Separated String To Cells In Column

Nov 1, 2012

I have extracted a string from my address database which goes like name,address1,address2,city,postcode,country

I need to display in Column B as:

name
address1
address2
city
postcode
country

how to do this using VBA.

View 2 Replies View Related

Macro Combines Cells Into String But Puts Undesired Comma At The Beginning?

Jul 21, 2014

I have a macro that takes the column of data from D on Sheet 1, pastes it into A on Sheet 3, gets rid of duplicates and then, in B1, combines all the numbers from A into a comma-separated string. The only problem is that it puts a comma at the beginning of the string. Is there something I can do to make that first comma not be there?

View 5 Replies View Related







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