Expanding Out Lists Of Text With Commas?

Apr 5, 2013

So, I have a list of data like this:

Country of Origin


Obviously I can do this using text to columns and transpose but typically I'm dealing with much bigger sheets where being able to do this automatically is a big time saving.

The column with commas in it can vary in position so I'd like this to work on the cell/column that is selected.

Ideally I'd like it to work it's way down the sheet expanding out every comma separated list in the selected column in this fashion but even something that worked on the current cell would be handy.

I think roughly I need to split the cell into an array using the comma as a seperator, possibly scrub the spaces from the text, copy the row as many times as there are things in this array (using ubound?) and then transpose the array into an area starting at the selected row and going down as far as there are things in the array but a bit lost with actual implementation.

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;


View 3 Replies


Excel 2010 :: Double Clicking Rows Is Not Expanding To Wrapped Text?

Mar 31, 2014

My cells have wrap around text enables. However when I double-click the row, it does not automatically adjust the row height to accommodate all of the text. It shrinks to a small row height. How do I fix this?

View 1 Replies View Related

Converting Text To Numbers With Commas In Them

Oct 30, 2008

I want to convert numbers that were entered into a spreadsheet with commas in them. Since they were entered with commas of course excel reads them as text.

How can I convert them to numbers so I can use formulas.

View 9 Replies View Related

Inserting Text Seperated By Commas.

Jul 28, 2006

I need to insert text seperated by commas into excel. Text goes something like this:


I need to insert the text so that go, run and jump are in column 1 and back, hide and dive are in row two. I used to know how to do this but I forgot.

View 4 Replies View Related

Export Data To Text: Space After Commas

Nov 10, 2006

I would like to export some data from excel to a text file. An example of text in excel is as below. which each number is in a separate cell.

I tried to exported it as csv format and open it with text editor. it become

however I would like the text file looks like this with a space between each comma.
1, 2
1, 2, 3
1, 2, 3, 4

What the best way to do it?

View 2 Replies View Related

Prevent Multiple Commas In Text File

Nov 30, 2007

I have a spreadsheet that is produced by a macro. This spreadsheet has a table of 30 rows and 24 columns pasted into it from another spreadsheet. The first 6 rows of vary between containing 1 and 6 columns of data. The next 17 rows always have 24 columns of data. The last row has 1 column of data. The macro saves this spreadsheet as a .csv file. The macro works great, but when I open the .csv file in notepad all the rows have 23 commas, even for the rows with only a few columns of data. Here is a shorten example of the file when opened in Notepad....


Is there anyway to have the macro not insert the commas in the first 6 rows for columns on the right side of the last column of data? I need the data to be formatted in the format shown below.


View 9 Replies View Related

Vlookup Comparing 2 Text Lists

Dec 2, 2008

I have two separate columns of text and want to identify similarities/matches. vlookup false is good for exact matches but vlookup true is too approximate - is there a way to hone vlookup true to match first 4 or 5 text characters rather than just the first? If vlookup no good for this what is best function for identifying approximate matches in two separate text lists?

View 2 Replies View Related

Dependent Lists And Manual Text Entry

Sep 27, 2011

I am relatively new to Excel. I have figured out how to use named ranges as lists and use INDIRECT Data Validation to have a list's selection made in one cell populate a new list in an adjacent cell.

A1 contains a list of "Characters" (The choices are "Numbers", "Letters", or "Other")

Based on the choice in A1, B1 has to populate with the following:

- A list of available Numbers to chose from (1,2,3..etc)
- A list of available Letters to chose from ( A,B,C...etc)
- Text that instructs the user to enter text into the cell manually

(For example, their "Other" character might be something like "@#$%^&*!". (Not unlike my mental state trying to figure out this problem.) I want them to see the instruction to "ENTER CHARACTERS HERE" and then be able to type in the cell.

So I need B1 to either populate with a menu that is dependent on the menu selection in A1, or allow for free text entry depending on if they chose the "other" option in A1.

View 4 Replies View Related

Dependant Lists Using Text Values Not Listed In Worksheet

May 28, 2009

I have seen in the forums where lists are created by refering to column values in other worksheets.If you create a list by entering text values can you make other lists dependant to those values and can those lists also be made up of text lists?

All the examples I have seen here use lists that exist in some other worksheet

I use lists which use text values directly and not from a worksheet. I'm wondering how to make a secondary/sub list dependant on each value selected so say if text value "A" is selected in list 1 then this only allows the list for "A" to be available for list 2 in its corresponding cell (the next one along)

View 9 Replies View Related

Add Or Remove Info To Two Lists In Sheet1 And Sheet2 And Difference Between Two Lists

Mar 29, 2014

My problem in the given excel file

1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list
2. Sheet2; how to remove "B000" or "B00" from the list
3. Sheet3; how to highlight difference or find out the difference between to lists


View 10 Replies View Related

Expanding An IF Formula

Oct 23, 2009

[qoute]I have five cells that I am working with cells A,B,C,D & E. cells B & C are time formated. Cells D & E are number formated and equal the number of hours between cells B & C. the difference is that if cell D is less that 2 hrs then cell E is to equal 2 hrs but if cell D is over 2 hrs then cell E equals the value of cell D. All this works fine but I am needing to add in the value of cell A. So if I have say CT in cell A then cell E will equal the value of cell D no matter if is less than 2 hrs[/quote]

View 5 Replies View Related

Expanding Data

Aug 9, 2009

in this example below i have record entrys of tps,rpm,etc etc in time.
typically 4 records every 1 second.

how can i make the data 10 rows for every second?

i.e can we take the column readings after D i.e row2 then at row4 and then insert 8rows between the 2 and enter averages.

1353ecu *ABCDEF1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPM21########13:47:190.55517.25491406.2532########13:47:190.80416.078431531.2543########13:47:191.03716.470591437.554########13:47:191.27223.529411343.75 Excel tables to the web >> Excel Jeanie HTML 4

the whole sheet is pretty big
1353ecu  ABCDEFGHIJKLMNOPQ1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPMBatteryCoolantTempAirTempTimingAdvO2SensorSpeedInjDutyCycleKnockSumLoad1BOctaneFlagknock_flag21########13:47:190.55517.25491406.2513.63938195.8122210.956483.2854850031.2100032########13:47:190.80416.078431531.2513.71271195.8122200.897923.2854850025.2100043########13:47:191.03716

View 43 Replies View Related

Lists, How To Make Drop-down Lists Optional

May 13, 2009

Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.

Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.

View 2 Replies View Related

Print Area Keeps Expanding

Mar 29, 2014

copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?

View 2 Replies View Related

How To Make Self Expanding Table

Jun 19, 2014

Is it possible to make a self expanding table. What I have is a sheet with a table with loads of biking tracks with track number, date, nr of miles and so on...

I'd like to make a table on the second sheet so that I can see only the tracks that auto fill as I put in info about number of miles or date or track number.

And I'd like it to auto expand so I don't have a lot of wasted space on the page. I'd like the TOTAL amount of miles, when i put in multiple tracks, to move up or down when I add or remove tracks...

View 6 Replies View Related

How To Stop Row Heights Expanding

Jan 7, 2014

This is probably something very simple, but I need to find a way to stop row heights expanding when text in a cell wraps. Basically, I have a spreadsheet of projects, and one column is for 'notes'. When the text in this column starts to wrap, Excel automatically increases the row height to make all the text visible - but I don't want that to happen.

I guess there's a button to press somewhere, but I can't find it!!!

View 4 Replies View Related

Expanding A Named Range

Jan 24, 2007

I have a bunch of nameds ranges that are essentially entire rows. For example, Rows 1 through 5 are named range "Range_A", "Range_B" is Row 10 through 20. I was looking for a way that I could have the value of one range assume the value and size of another range.

For example

Range_A = rows 1 through 5
Range_B = rows 10 through 20

Now, if I use the following macro all that happens is the first five rows of data is copied over to Range_B while Range_B's other 5 row values are not copied over.

Sub Example()
Range("Range_A").Value = Range("Range_B").Value
End Sub

What I want to have happen is have of Range_A expand to the same number of rows the other range has (which is 10 in this case) and assume the value of Range_B. Is there a way to do this? In my workbook I have a number of ranges that need to be replaced with other ranges and I want them to assume the size and values of the range they are supposed to equal.

View 6 Replies View Related

Auto Expanding Combo Box

Jun 9, 2007

I have a script that converts any List based Data Validation cell into a combo box when you click on it...What I am trying to do now is find a bit of code that will auto expand the combo box.

View 3 Replies View Related

Expanding Only One Field In Pivot Table?

Jan 9, 2014

I have a problem in expanding/collapsing fields in pivot table.

I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).

The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?

View 2 Replies View Related

Auto Expanding Drop-down List?

May 6, 2014

I have a list (C5:C16) and I want people to be able to add more names to the bottom and it add to a dropdown list on another page.

View 6 Replies View Related

Automatically Expanding Cell Ranges?

May 8, 2014

The table below has three rows and 7 columns. Row 3 shows cumulative sums of the values in Row 2. Sometimes, Row 2 may not have values (not even a 0). The formula for C3 is =SUM(B2:C2) and =SUM(B2:G2) for G3. The challenge is that I have a very long row and summing it individually would be inefficient.

Is there a way of automating the formula so that the cell ranges expand automatically which I could use to fill to the right?

1Row 1123456
2Row 21041503
3Row 3115202023

View 5 Replies View Related

Expanding The Ruler's Comand Line?

Mar 24, 2009

When not programming I sometimes use very long commands (e.g. multiple conditions with data importing from closed files). The command line in the rule expands such it hides the columns headings making the writing very uncomfortable. is there a way to force the line to be wider without spilling into the data (like another ruler line)?

View 3 Replies View Related

Expanding Named Range Using Offset

Nov 12, 2011

I have data coming into my sheet from an Access file into columns b:l which automatically get covered by a named range which changes according to the size of the data coming in.

What I'd like to create is a named range which includes this original range as well as columns a and m:p. Is this possible using Offset at all?

On top of this my data from Access comes into row 2 and I'd like the named range to cover row 1 too.

View 3 Replies View Related

Expanding Increasing Phone Numbers?

May 20, 2012

I have thousands of phone numbers that hundreds of them are look like this:

123456-8 that means : 123456,123457,123458 or this
123456-57 that means : 123456,123457

There are not any standard of numbers after dash (-) in my data.

how can I expand them to new "rows"?

bob 123456-8 ->
bob 123456
bob 123457
bob 123458

View 9 Replies View Related

Macro To Create Expanding Table

Jul 9, 2012

So I have recorded a macro that filters for certain criteria in a master list of projects, then copies the projects that meets this criteria and pastes it into a timeline in another worksheet. However, because the list of projects gets bigger and smaller when the filter criteria changes, right now I have to create twenty extra rows so that the pasted list doesnt ourgrow my timeline. How I could input a few lines in my macro so that the table for the timeline expands as the number of prjects grows or shrinks.

One method I have tried is to copy the filtered data and "Insert Copied Cells" into the table, however I don't even get the option to insert copied cells after I have copied the data...

View 3 Replies View Related

Expanding Named Ranges And Access

Jun 6, 2009

Why do my named ranges expand each time I import data from Access? I then have to go my named ranges and reset them. The reason I am using named ranges is because of the indirect functions I am using.

View 9 Replies View Related

Average Of Last 12 Rows Of Expanding Data

Oct 20, 2009

I have a table of data to which new figures are added each month as follows:


"Month" is in A3, "Headcount" is in B3. Figures are on rows 4:24

What I need is a formula that will average the last 12 rows of data so that when a new record is added, the average will update automatically to include the newly added row, but still only look at the 12 rows (i.e. for the data as listed it would average rows 13:24 (Oct-08 to Sep-09), but when data for October is added, it would average rows 14:25 (Nov-08 to Oct-09).

View 9 Replies View Related

Expanding Code To Multiple Cells

Jan 22, 2007

I have this source am using in a validation list.
=INDIRECT(SUBSTITUTE($A$2," ","_")) and the source points to A2 cell.

My problem is I need to expand this code to the next say 50-100 cells below and when I try to code the cell the next cell refers to the same A2 when I need it to refer to next corresponding cell (Eg A3)


what I need is below:

Column A Column B

View 7 Replies View Related

Expanding Details From A Pivot Table

Feb 5, 2007

I would like to use some VB code to search a Pivot Table, find a specific value and show the details relating to the value. I used the following code I have obtained from another thread in the forum

Sub Expand_Pivot_Values()
Dim rngFind As Range
Dim rngPTData As Range, rngPTC1 As Range
Dim rngF As Range, rngP As Range
Dim strFind As String
Set rngFind = ActiveSheet.Range("J3:J6")
'set the pivot table data range - see help file for property description
Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange
'set start cell for find
Set rngPTC1 = rngPTData.Cells(1)
For Each rngF In rngFind.Cells
'set variable to find........................

This code works fine but only identifies the first time the value appears in the pivot table and shows detail relating to that value. I need add to the code to search the pivot table and find recurring values. For example if the value is "2" I would like to show detail on each "2" value there is in the pivot table.

View 7 Replies View Related

Expanding Range For Chart Source

Jul 6, 2007

I have a problem setting up an offset range on my charting example ( attached).
My X axis will always be row A

I want my charting range to be more than just the one column and since the months will keep expanding I'm looking fopr the easiest method to do this.

My range could start at 1 row and finish at any other. User Defined.
Bu I want that whole range to be included in the charts. This is a stacked chart.

So in my example I'd liek the shaded bit as a stack chart. 3 bars for 3 months with 4 stacks in each one.

View 9 Replies View Related

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