Separating Items With Comma

Sep 14, 2009

i have many many cells that have text like the following:

A,B,C,D,E,etc...

what i would like is to have it show like the following instead (within the same cell):

A
B
C
D
etc...

i know i can do this using ALT+Enter to make the separation manually....but there are many many cells....can this be done automatically?

also, it is possible that some cells would have only 1 item so in that case, it should be left alone....

ADVERTISEMENT

Merge The Content Of 2 Cells Into 1 (with A Comma Separating The Content)

Jan 29, 2007

how to combine the content of 2 cells into one cell and have the information separated by a comma.

For example:

CELL 1:
Software 1

CELL 2:
Spreadsheet Software

The desired results is:

CELL 3:
Software 1, Spreadsheet Software

View 3 Replies View Related

Transfer Chosen UserForm ListBox Items Into Worksheet Textbox As Comma Separated List

May 6, 2009

I want to select items in a listbox and transfer those items via command button in a textbox. The listbox is already filled. I have no idea how to realize that.

Attached is the form I created so far. I copied everything together and matched it up for me. It's probably not the best way but it works. I marked the section where I need help in yellow.

View 9 Replies View Related

Lookup Comma Separated Data And Return Comma Separated Answer

Mar 19, 2014

Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.

Sample file attached : Book1.xlsx

View 3 Replies View Related

Conditional Formatting IF Function To Identify Past Due Items In Red And Items Completed

Aug 13, 2014

I have a spreadsheet that lists a set of actions and the days they need to be completed.

The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.

I have a dashboard on a separate worksheet, showing if the item is complete and the due date.

Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.

I have been going through my books and trying to solve this, but I run into a different issues.

Even if for now If I can get the main worksheet to display the color conditional formatting that would work

View 2 Replies View Related

Count Items In Column That Match Multiple Data Items?

Mar 27, 2014

I need to count the total number of times 4 different values appear in a column. This formula works for one value:

=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)

Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.

View 6 Replies View Related

Select Multiple Items In List And Then Print Those Items?

Dec 26, 2013

I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.

In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.

View 3 Replies View Related

Excluding Selections: Added To The 20 Items And Not To The 3 Items

May 23, 2006

If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..

View 4 Replies View Related

Separating The Dates.

Feb 22, 2010

I have a form where users can input a Date by utilizing 3 drop down combo boxes. Once they've made their selection that Date gets written to a cell on the worksheet. If for some reason they need to change that Date, they would reopen the form and select a new Day month or year.

I would like the three comboboxes to show the D/M/Y previous written to the worksheet. But I would have to split up the date that has since been concatenated.

View 8 Replies View Related

Separating Out Address Using VBA

Jun 28, 2013

I have a spread sheet with thousands of addresses in them.

It does not hold the state however.

I need to separate each part of the address into it's own cell on the same sheet.

Keeping the first part ONLY of the address in the original place the address was in.

However not having the city, or zip in that cell.

For example:

E2 holds the address: 19505 45th Ave. W. Suite A, Lynnwood 90821

(There is always a comma before the city. However sometimes there is another comma before Suite...)

I want the end result to be:

E2: 19505 45th Ave. W. Suite A
I2: Lynnwood
K2: 90821

Then I would like it to loop through all the address's in the "E" Column until it has edited all the address's. It is sad to say but I have been working on this for over 48 hours... Can't believe no one online hasn't come up with a clear answer on how to do this. At this point I am looking for the code to make this happen in Excel VBA.

View 9 Replies View Related

Separating Second Occurrence

Sep 16, 2008

[data] ....

I'm having a problem seperating the prices in Colomn H into three seperate cells C,E & G
The first figure in c is easy enough if a little long (any possible tidier solutions?)
The second Im not happy with it is clumsy using the fixed lenght figure "5"
The last I cant figure out probably because Ive done the second incorrectly!
Finally I can not see why figures apear in away team.

View 9 Replies View Related

Separating Data In A Cell?

Aug 20, 2014

I've been tasked with separating data within an Excel cell into it's own row. The difficulty I am having is, I need to keep the ticket number each piece of data within that cell is linked to. For example, I have Column A that has the ticket number, and column I has the serial numbers of each piece of equipment linked to that ticket number. I would like the serial numbers separated into their own row, but still keep the ticket number in which that equipment is connected to.

Here's an example: Column A Column I
Ticket # Serial Numbers on Account
23999866 PAFR06598067 - MOPCUDTAM

Would I need a formula to do this, or is this something I can do using one of the Ribbon commands?

View 9 Replies View Related

Separating Information In Cells

Aug 6, 2007

I have rows of cells with the following information..

Row 1, Column 1: Address City/State
Example: 111 Old Creek Road Stanton, VA 25523

Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format.

Row 1, Column 1: Address
Row 1, Column 2: City/State

or ..

Row 1, Column 1: 111 Old Creek Road
Row 1, Column 2: Stanton, VA 25523

I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs:

View 9 Replies View Related

Separating Or Sorting Data

Sep 22, 2008

for example:

Column A.

@yahoo.com
@hotmail.com
@live.com
@msn.com
@yahoo.com
@hotmail.com
@live.com
@msn.com

how can i separate all datas by domain without having to cut and paste them manually.

View 7 Replies View Related

Separating Cells In A Worksheet

Jan 2, 2009

Set up a worksheet so that it has 2 separate sections in the same worksheet ????? I am wanting to have 2 different sets of information in the same worksheet - and need the columns to be different sizes based on the information i want to put in. Is there any way to have this done - as I know that if you change the column width it does all columns - and i only want to do select columns. For example for section 1 - i would like to have the column widths as follows:

A1 = 20
B1 = 40
C1 = 10
D1 = 20
E1 = 40
A3 = 20
B3 = 40
C3 = 10
D3 = 20
E3 = 40

This is where i want to put details such as names, dates and results.

Then for section 2 - i would like to have the column width as follows:

A6 = 10
B6 = 45
C6 = 15
D6 = 30

This is where i want to have a set of questions to answer.

View 4 Replies View Related

Separating Recurring Names

Apr 25, 2009

Is there any formula or formatting that will list in a seperate table all names that occur equal to or more than a specified number of times?

eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table on a seperate worksheet.

Gus
Bob
Mike
John
Gus
Nick
Mike
Gus

So Gus and Mike would be listed in the other table on the other worksheet.

View 9 Replies View Related

Separating Numbers Into Different Columns?

Sep 21, 2013

I have a column with random times that are not seperated by any commas or spaces how can i seperate them into different columns. For example in A1 i have 12:3213:5420:32 this represent three different times 12.32 13.54 and 20.32 (each time has the same format as shown) and i want to display these as B1=12:32 C1=13:54 and D1=20:32 then do the same for the rest of column A?

View 3 Replies View Related

Separating Date And Time

Oct 29, 2013

I need some dates showing in the correct format.

I have two worksheets, the first will have a date as shown in the attached worksheet in cell A2. This would have been pasted as a value from another source. I need this to be seperated and to show as in cells D2 and E2.

Would be ok with either a formula or macro to do this.

View 2 Replies View Related

Separating Words In A Cell

Feb 26, 2013

I have a cell containing text like this

Ph(Off):* 5754112-5750441 ,* Cell:*0300-8406693*******,* Fax(Off):* 5712685*

I want to separate them into different cell though which every word or number contain his own cell

like
A1 = Ph(Off):* 5754112-5750441 ,* Cell:*0300-8406693*******,* Fax(Off):* 5712685
then i want it in this way
B1 C1 D1 E1 F1 G1
Ph(Off): 5754112-5750441 Cell: 0300-8406693 Fax(Off): 5712685

View 2 Replies View Related

Separating The Records Depending On The Value

Dec 22, 2008

I have attached 2 spreadsheet :-

1. BounceBackMaster.xls
2. BouncebackMails.xls

I need to excute the macro from master file asking the input file. Input file will be the Mails spreadsheet. In the master spreadsheet there will be "TAG" column, depending on value it need to check the value in the Mails spreadsheet "MessageContent" column after subject value. It need to group all tAG value, each sheet need to be created with TAG value and related need to be copied from "BounceBack" sheet to created sheet.

Example:

Step 1 :- Macro is executed from "BounceBackMaster.xls" and asked for input file, input file will be "BouncebackMails.xls"

Step 2 :- From "BounceBackMaster.xls", First TAG value will be readed " Monthly Account Statement" is check in "BouncebackMails.xls"........

View 3 Replies View Related

Separating TEXT Into Different Columns

Dec 2, 2008

Seperating TEXT in different columns placed at non-fixed location in a cell...

I have some data which contains people's name and the places ( i.e Cities) mixed which I need to seperate, however the challenging part there is no consistency in data as the city can appear first in the middle , last or even just the city name in the cell but it needs to be seperated ...

In Column B I have the following type of DATA ...

View 11 Replies View Related

Separating Information In A Cell

Sep 26, 2011

I can’t seem to work out how to pull data relating to a particular title from one spreadsheet, to another, separating the information on.

For example, column A lists names, and column B lists tests scores for each person. However each person has done several tests, and the tests are listed in one cell, separated by a carriage return (not comma etc, so can't do a delimited function).

I want to create a new spreadsheet with names still in column A, but the test scores separated. So Column B = Test one, Column C = Test two, Column D = Test three, etc. In each column I would like only the test score listed (so not the test title - I will put this in the first row as the column header)

Is this possible?!

View 1 Replies View Related

Separating Data From Days?

Jul 29, 2012

What I am looking for is a macro that can separate the data introduced manually in the left table (see attach file) and order it in the table from the right hand side as I have done it manually in this case. The base of it is to have at the end all the trips separated from days (days going from 0:00 hrs till 24:00 hrs). Maybe is a bit confusing but in the example I have attached I presume is going to be easier.

View 3 Replies View Related

Separating Text Into Different Columns?

Jun 24, 2014

I want to separate the text into columns as in Table

Name
Name
Contract
Ref. No

[Code].....

View 5 Replies View Related

Detecting And Separating Integer

Dec 1, 2008

A1 : Style 998 45702B WRAP DRESS 1 0 0 0 1

Try to accomplish:

I would like to remove the "1" in between "Dress" and "0"

in other word remove integer from string but starting from right only.

B1 = should contain 1

View 9 Replies View Related

Separating Alpha And Numeric

Jan 21, 2009

I have data in column with combination of alpha-numeric like "Member # 308166 RCI Number 4801-60436". can I split this without using copy and paste?

View 9 Replies View Related

Separating Characters By Space

May 1, 2009

I have following kinds of texts in a column.

MISCELLANEOUS
EQUIPMENT
JBKU348597-3
T-225
75984

I want to convert above texts with space between every character as follows:

M I S C E L L A N E O U S
E Q U I P M E N T
J B K U 3 4 8 5 9 7 - 3
T - 2 2 5
7 5 9 8 4

View 9 Replies View Related

Separating Commas And Parenthesis

Jul 20, 2009

I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.

View 9 Replies View Related

Vba, Separating Ranges With A Border

Feb 7, 2007

I have a spreadsheet of data, and all entries are grouped by different unique reference (three digit number). I need something in VBA so if the the unique reference in the columns are the same, to place a border over the ammending data. The actaull; spreadsheet is sorted ascending by the unique references so all the entries are already grouped together. EG - So if I ran the code it would put a border around the group of rows for unique reference 126.

Unique reference
126
126
126
126
128
128

I attempted using if activecell offset, but i dont think the logic works. I ran a macro for the border, it is below.

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With.........................

View 7 Replies View Related

Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

Jul 17, 2009

I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

View 9 Replies View Related

Separating From Multiple Rows Into Columns

Mar 25, 2014

I have an interesting problem I've been presented with and rather than try formula after formula I thought I'd propose it to you all (see attached sheet).

I have 7269 rows with 1930 distinct claims. I need to pull out the Primary issue (done that) then across from it put in each and every secondary issue (from col C). The largest # of claim lines is 89 (see E1). So in theory I need to find that claim and put all the secondary issues in the next 89 columns from row 4990 beginning in col H.

I've done a couple examples of what I'm looking for in rows 2, 4 and 5 and 8 but beyond that ...

So every place where there is a value in col F I'd like the list of secondary issues in the same row.

View 4 Replies View Related

Separating Words In One Cell Into Many Cells

Jun 12, 2014

All words are in Cell A, separated by the ";" symbol. I want separate into different Cells. Example... Lead_ID in Cell A1, Application Data in A2, Date Purchased in Cell A3 and so on.

lead_id;application_date;date_purchased;first_name;last_name;email;day_phone;work_phone;
best_time;city;state;zip;addr;cred_rating;cred_score;yearly_income;prop_state;prop_type;purpose;
loan_amount;prop_value;ltv;mtg_bal1;mtg_bal2;services;credit_repair;foreclosure;debt_management;comments

View 5 Replies View Related

Filtering / Separating Specific Datasets?

Jul 10, 2014

All of the strings are in one column and are in General format. They contain a few different types of strings: some only numbers, some numbers separated by commas (which makes them NOT numbers according to ISNUMBER), some text only, some text and numbers (separated by comma), some are 3 numbers, each separated by comma, etc.

ldJBlt9.jpg

What I'm looking to do:I want each of these distinct types filtered out someway. I've already done ISNUMBER to filter out the values which are numbers only, no commas. Perfect.

For the next step, I'd like to filter out cells which have only numbers and commas. The comma itself makes them considered not numbers, so how do I add the condition that I'm looking for cells with digits and a comma? A wildcard?

Edit: I've found the following formula which can check if it contains a comma, but doesn't specify between digits or text. Just commas. =IF(ISNUMBER(FIND(",",A1)),"TRUE","FALSE")

View 1 Replies View Related

Separating Information For Text To Columns

Oct 2, 2008

I have a list of 2500 companies. In each cell there is the company name, street address, and phone number. And of course, each one is of varying length. I need a way to put the company name, street address, and phone numbers in seperate columns. I tried going to text to columns but it wouldn't work due to the varying lengths.

View 5 Replies View Related

Separating ODD And Even Rows Into Separate Column?

May 13, 2013

I have one column of data, I would like to separate the ODD and Even row values into two separate column, such that the data will appear staggered.

ie. this is my data in column 1

1
2
3
4
5
6
7
8

would like to separate it into columns 2 and 3 as follows

1
.....2
3
.....4
5
.....6
7
.....8

I believe I need to use the ISODD, ISEVEN function somehow but can not figure it out.

View 4 Replies View Related

Separating One Column Of Data Into 3 Based On Its Value?

Jan 16, 2014

I am trying to divide one column of data into three. I can't change the order of the data because it is set up as a schedule at my work.

Here's how the info is sent to me, lets say columnA Row2:

500001
500003
500002
700002
700003
700001
800003
800001
800002

Here's what I need my spread sheet to do.....lets say in column's C, D, and E (all in Row 2)
C D E
500001 700002 800003
500003 700003 800001
500002 700001 800002

View 6 Replies View Related

Separating Text & Numbers In Different Columns

Jan 27, 2014

I have to separate text & numbers in different columns.

e.g. 349,910 American Capital Ltd. (a) 4,902,239

i want all 3 data in separate columns, there is a series of data like this wherein numbers are there both at begining & end & text in middle.

View 11 Replies View Related

Copying Rows And Separating Tables

Jan 9, 2014

I have an excel sheet that I'm trying to format. Basically, the excel book contains sheets (with the months), and each sheet contains a list of clients, their package, their status, date assigned, and date updated.

I've made another sheet (Sheet2) that fetches everything and consolidating it to one sheet. Sheet 2 also cleans up the data to display only the items that are 'completed'.

The issue I'm having now is in splitting the list (assuming it has been cleaned up to show only the 'completed' items. I would need to split the list first by the date they were updated, and then if they were assigned the same date or not. In short:

--Get all items with date updated = Month1
----If date assigned = Month 1: List items under table 1
----If date assigned < Month 1: List items under table 2

I've used macro on the excel (and I'm not sure it's efficient enough. I'm not proficient with VB, so...). I'm not sure if it will work on Mac.

View 14 Replies View Related

Separating Macro Command Lines

Nov 4, 2008

I have a problem with multiple, similar formulas in a macro. Each of these formulas, depending on the active cell, selects a certain range of cells and prints it.

The problem is, no matter which active cell I'm on, the macro activates all lines of the formula and separately prints each range. Is there a way to "separate" these lines in the formula so each range will print separately, depending on the active cell?

Here's the

View 8 Replies View Related

Separating And Converting Strings To Range

Nov 10, 2008

I am trying to develope a search macro to go through sheets of a book looking for strings separated by comas in an Inputbox. For example, The data in the inputbox may be inputed as: ahu-1,vav1,erv-2,etc(Number of items may vary). Then to assign variables(range) to each in order to use the "Find" function.

View 2 Replies View Related

Separating Rows By Date Range

May 18, 2009

I need to separate out some rows by date into new worksheets, but I need the dates to be a range. Our cycles are separated into periods, which last about 1 month, so I need one new worksheet for each period. I was thinking about having like a cover sheet that defines the exact dates (like column A has the period title "P1, P2, P3,..." and column B has the start date and column C has the end date).

Then on the next sheet is where I would keep my Input data. So I need to separate the rows of the input data sheet out by the periods. The date for each data entry is kept in column M. I have attached a sample workbook explaining what I tried to describe above.

View 6 Replies View Related

ADVERTISEMENT