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


Text To Columns (vertically Stacked)

Had a quick browse through the forums for an answer but as it is quite hard to describe i cant quite find the answer.

Basically I need to split some cells but they have stacked text in them i.e

Cell a1 shows:

part 77777 x 20
5x s452563

Cell b1 shows:

1x 254684564
3x 4481211111 & 5 ea g8373


When i run the text to columns function i only get the first line of the data, i could ideally like to split the data by spaces and/ or line breaks.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
(Userform) Textbox, Center Text Vertically?
We can center horizontally with TextAlign (Left, right or center). Can we center text in a textbox on a userform vertically? I am working with multiple fonts, when a user selects a font I attempt to format a textbox as a display to show what is being created (Best WYSIWYG as I can). I have this particular font that is just ugly but is required. My textbox is set for a 12 point font but the displayed characters partially appear below the lower portion of the textbox. Think of cutting off about 1/3 of the bottom of all text in the textbox.

In my textbox it seems like the text could be moved up (some type of top margin?). All other fonts appear to display in the textbox vertically central, so I believe its the particular font selected causing the as displayed anomaly.

View Replies!   View Related
Vertically Center Text In Userform Label
Is it possible to vertically center the text in a userform label? For example: I have a label that's 22px high, but the text is only 10pt -- at the moment it just hangs out at the top of label looking bad.... I'd like to place it the middle if possible.

View Replies!   View Related
Align & Center Text In Cells Vertically
I know how to center text from left to right, but how about from top to bottom?

View Replies!   View Related
Stacked Columns With A Total
I have a stacked column graph showing data for 5 categories for 4 years (4 columns, 5 categories each).

I would like to display a Total above each column. I was able to do this on a single chart by just adding a text box and manually adding the total amounts. However, I would like this to be updated dynamically, as the data will be changed frequently.

Is it possible to:
(a) Have the total display as a data lable, or
(b) Have a text box containing a cell reference

View Replies!   View Related
Columns & Stacked Columns Chart
I am trying to get an Excel chart to include columns AND stacked columns in the same chart with no success. I have attached the Excel chart where I could only get stacked columns and I had to use a line chart for the secondary axis. I could not figure out how to get the secondary axis to be a set as a column just like the primary axis. I thought this could be done in Excel, I have searched the forums and tried to play around with the gap width and/or include a blank column but I still haven't been able to get this to work.

View Replies!   View Related
Stacked Chart With Double Columns
According to my provided data, draw a stacked chart with double columns, each bar includes four types of data(Internet, PBS,Branches and Mail-in/Fax-in) and the x-axis are 1Q06, 2Q06 and 3Q06 repectively. It is urgent,pls provide the template for me. Many Many to all of you.

View Replies!   View Related
Stacked Cell Text
Is it possible to have stacked text in a cell? Turning wrap text on doesn't quite work since an "ol" is the same width as an "m".

Example: P

View Replies!   View Related
Dragging Vertically
In row 3 I have values horizontally. (A3 to Z3)

i link C5 to A3.
If I drag it vertically it does not give the correct values.
Is it possible to drag it in a correct way?

I tried =INDEX($A$3:$X$3,ROWS($A$3:$A3))

View Replies!   View Related
Drag Vertically And Copy Cells Across?
I am trying to link from one spreadsheet to another and drag the cells down to copy the forumula, however I want to drag vertically on Sheet 1, and Copy the values horizontally from sheet 2.

For example, in sheet 1 I link cell A1 to equal cell A1 in Sheet 2. If I drag down the formula in sheet 1 A1:A10 then it will copy the values in cells A1:A10 in sheet 2.

Now what I want it to do is for me to drag the formula in cell A1 down to A10 in sheet 1, but for this to return the values of A1:J1.

View Replies!   View Related
is it possible to concatenate the contents of several cell vertically into a single cell? like using (e.g. B47&B48&B49&B50&B51&B52) in a statement but make it vertical? and make some parts blank if it does not contain data.


(please see attached file for reference)

View Replies!   View Related
Freezing Panes Horizontally And Vertically
I know I can freeze panes eithe across a column or row but is it possibleto do both at the same time so that I can have a header row and a few columns on the left of the screen frozen?

View Replies!   View Related
Freeze Horizontally And Vertically At The Same Time
How do you freeze horizontally and vertically at the same time?

View Replies!   View Related
Automatically Size Textbox Vertically Depending On Content
Is there a way to control the vertical size of a textbox, so that we could type in a List of Instructions to our operator, and the textbox would resize depending on the numbers of instructions in the box. Also the items in the cells beneath the textbox would need to move down, to allow for the resized textbox.

View Replies!   View Related
Text File Import With Text To Columns Splitting Same Text Differently
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View Replies!   View Related
Autofill: Macro That Merges A1 Vertically As Shown In My Spread Sheet To The End Of Column B And C
Each sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.

View Replies!   View Related
Text To Columns/time Formatting Split Them Into Columns
I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:

hh: | mm: | ss

I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM

View Replies!   View Related
Two Stacked Bar Chart
I am trying to define a chart of TWO Stacked Bars. (it will be a little difficult for me, with my bad English, to explain but I'm sure you will understand. My target is a chart as shown in the embedded picture. Take a look at sheet1:

This is a 75% completed chart.
I managed to present the first 2 stacked bars. (light & dark green).
Then I added a blank column ("D") and declared it as a "bar" on the 2nd. Y Axis.
(This "D" series has now two values, of 5, which must be "Zeroed" when the chart is finished).

I managed to add the first part of the 2nd. Stacked bars. (Orange)
I played a little with the Width and Overlap and got the result shown in Sheet1.
(When The "two fives" will change into 0 - the two orange bars will start from the X-Axis (Zero "Y" level)/ So far so good.

What I need now is to add the last Series (F2:F9) to be put ON TOP of the Orange Bar but without success. If you'll take a look at Sheet2 you will see what I came up with. I DO NOT want them to be inside each other. I want them as in the embedded picture (side by side). In addition I would love to present the Labels:
Newspaper, Internet, Relevant, Non-Relv. Inside the chart (if possible)

View Replies!   View Related
Stacked Bar Chart
I am trying to define a chart of TWO Stacked Bars. (it will be a little difficult for me, with my bad English, to explain but I'm sure you will understand. My target is a chart as shown in the embedded picture. Take a look at sheet1:

This is a 75% completed chart. I managed to present the first 2 stacked bars. (light & dark green). Then I added a blank column ("D") and declared it as a "bar" on the 2nd. Y Axis. (This "D" series has now two values, of 5, which must be "Zeroed" when the chart is finished).

I managed to add the first part of the 2nd. Stacked bars. (Orange) I played a little with the Width and Overlap and got the result shown in Sheet1. (When The "two fives" will change into 0 - the two orange bars will start from the X-Axis (Zero "Y" level)/ So far so good.

What I need now is to add the last Series (F2:F9) to be put ON TOP of the Orange Bar but without success. If you'll take a look at Sheet2 you will see what I came up with. I DO NOT want them to be inside each other. I want them as in the embedded picture (side by side). In addition I would love to present the Labels: Newspaper, Internet, Relevant, Non-Relv. Inside the chart (if possible)

View Replies!   View Related
Stacked Column Charts
I'd like to create a stacked column chart that compares monthly results from the sales staff but can't get excel to show what I need.

Each salesman has a total monthly sales figure which includes an element of profit and I'd like to see the splits in a stacked column chart. E.g in May, Simon's total was 7295 of which 863 was profit.


The chart should have a column for each salesman and show both the total figure and the profit for each month in the same column so with May and June figures there should be 6 columns on the chart.

How do I lay the data out in excel to get the right graph?

View Replies!   View Related
Stacked Bar/Column Chart
i have a chart made but it will only read numbers what i looking for it to do is read cells filled. beacuse none of the cells will have numbers in them. example column I,J,K,L will have a letters in them. starting on row 2 on down till the end. what i'm looking for is if I2 has a letter in it it will read as one for the chart. and will ignore the other columns if K7 has a letter it will read as one and ignore the rest of the columns. this will go on till the end of the sheet. and all rows with nothing in them will be ignored. is this posable?

i think it is bacisly giving a value to a letter and having the chart inturpat that value as 1 and then adding them up. if posable i just thought of maybe the letter could be turned into a date to give an ongoing graph for the info. maybe thats the wisest thing to do to show what each day looks like. hmm i have to play around with it. just typing this out i think i figured a way to do it.

View Replies!   View Related
Dynamic Stacked Bar Chart
I am trying to develop a dynamic stacked bar chart, I have a consistent number of columns defining the x axis and a variable number of rows defining the y axis (the stacked data). Is it possible to generate a dynamic stacked chart. I have tried using dynamic named ranges but I can't get it to work for a stacked chart.

View Replies!   View Related
Stacked Column Chart
I have a spreadsheet where I have some data. The data is divided into two parts we have part A that is a figure and then I have a A total which is a figure which part A cannot exceed and then what I would like to do is to create a Stacked Column Chart where I have an A total column where the maximum amount is the total A and where I would like to show how much A has utilized the A total.

View Replies!   View Related
Stacked Bar & Line Graph
how to create a stacked bar & line graph together.

I know by default, I can choose to have a stacked bar, OR a line graph, OR a basic bar & line graph.

But I need to take a step further and have a stacked bar WITH a line graph.

options in which I could combine the above graphs?

View Replies!   View Related
Exasperated With 2007 Stacked Bar Chart
I need to create a chart showing a timeline from 0 hours to 100 hours. In this timeline, there has to be 8 seperate segments - ie, phase 1, phase 2, phase 3, phase 4, phase 5, phase 6, phase 7, phase 8. Each phase takes a certain amount of hours (the number of hours of each phase is irrelevant for now!).

how to format this data and then how to display such a chart? I need the timeline along the X axis.

View Replies!   View Related
Macro Delete Stacked Numbers
I already have a few macros I run in order to sort data. I am able to show problem data by using a macro to perform an output that puts the number "1" in a cell if there is a problem.

I'm looking for some help if someone could create/show me a macro that can delete stacked numbers. For this I mean if there is the number "1" in cell A1 and a "1" in cell A2, I want to delete the bottom of the two which means remove the "1" in cell A2 so that there are no 1's that are stacked. The number "1" in cell A1 and a number "1" in cell A3 is fine.

View Replies!   View Related
Stacked Bar Graph With Average Line
We are trying to create a stacked bar graph with an average line. Each time we try to add in the average data to the graph it adds it to the stacked bar. The stacked bar should represent the inpatient & outpatient cases at a hospital for the current year (months showing across the as columns). The line should represent the previous 2-yr average total. Anyone have an idea how to add a line to the stacked bar graph? It has us stumped.

View Replies!   View Related
Bargraph With Grouped And Stacked Bars?
This is the data:

jan09 feb09 mar09 .....
Division 1
Division 2
Division 3
Division 4
Division 5

jan08 feb08 mar08 .....
Division 1
Division 2
Division 3
Division 4
Division 5

The graph would be a bar graph with 2 bars next to each other per month. each bar would be stacked by division. The goal is to compare for example sales in 08 with sales in 09 showing 2 bars next to each other split by division.

View Replies!   View Related
Create Stacked Bar Chart/Graph
I need to plot a stacked chart. I have attached a sample of it. I need to plot, category As X and subcategory and and its percentage as Y (stacked chart). In the attached sample I plotted a chart. The problem is, the series name showing is not correct. It takes the series name from the top row.

View Replies!   View Related
Conditionally Color Stacked Bar Chart
I would like to display 50 sequences, S_1 ..S_50, with stacked bars. Every sequence can contain 5 different events, E_1 .. E_5, in a random order. Every event has a certain duration, t. Lets say the first two sequences are as follows (duration time is in breckets)

E_3(200), E_1(150), E_2(300), E_4(500), E_5(300)
E_1(200), E_4(150), E_1(300), E_5(500), E_2(300), E_2(300)
How do I get Excel to display all Sequences in one graph as a function of time, with the bar-stack-color displaying the kind of event. So if E_3 would be red and E_1 blue we would see
red, blue ...
blue, ...

View Replies!   View Related
Stacked Column & Line In Same Graph
I would like to make a graph in Excel, where I present both a stacked column and a line with two different axes. Including a clustered column and a line in the same graph with two different axes is easy, as this combination can be found in the custom types selection in the chart wizard. However, there is no such alternative as a stacked column and a line with two different axes. Is combining the two manually possible?

View Replies!   View Related
To Populate Three Columns With Text Based On Text Of Another Column
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.

For example

Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"

I am using MS excel 2007.

View Replies!   View Related
Extracting Strings Of Text From Columns Of Data(text)
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells

The data I require is :-

(a) The persons name which is always after the word Requester e.g. Requester Steve Robinson

(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)

(c) The Approving persons name which is preceded by Approved by e.g. Approved by Christine Hunting

See examples 1 & 2 below

Example 1

CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting

Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before

Example 2

CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers

Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.

As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text

View Replies!   View Related
Stacked Column Chart With Rate Line
Here's a sample of a chart that I want; However, I want the bar chart to be a stack chart as I have multiple "Failure modes" per month that I want to show the number(or count) of failures per month. Then I want the line chart to remain the Complaint Rate for that month.

I just can't seem to figure it out.

This chart pulls from a data set that looks something like this: ....

View Replies!   View Related
Stacked Column Chart Totals 2007
Would like to add totals to stacked column chart in Excel 2007.

I have followed same steps listed here [url] but cannot position data labels above column.

Excel only gives me the options Center, Inside End, and Inside Base.

I also tried setting label position with VBA:

For Each co In ChartObjects
For Each s In co.Chart.SeriesCollection
If s. Name = "Totals" Then
For Each dl In s.DataLabels
dl.Position = xlLabelPositionOutsideEnd
Next dl
End If
Next s
Next co

Also tried the following without luck:

dl.Position = xlLabelPositionAbove

For each VBA example I get following error:
Run-time error '-2147467259 (80004005)':
Automation error
Unspecified error

It seems like Microsoft has removed this positioning option for stacked column charts because it works fine for normal column charts. Anyone find a way to do this otherwise?

View Replies!   View Related
Swap Series In Stacked Area Chart
I have an interactive chart displaying stacked area charts for 2006 and 2007
I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006

View Replies!   View Related
Secondary Axis Creates Stacked Bar Chart
When ever I add a secondary axis to my chart my bar chart sub type changes to a stacked bar chart. How do I keep the sub type bar chart? I have attached an example. I have tried playing with the gap and width of the bars but to no avail.

View Replies!   View Related
Join Text Of 2 Columns Into 2 More Columns
how to add two columns of single words together, so that all possible word combinations are seen. For example:





So... I'd like Column3 to look like this:..........

The issue is I have about 100 words all together so there will be a lot of results! Is there a way I can enter a formula to do this?

View Replies!   View Related
Stacked Area Chart Series Colour Change
I have a stacked area chart located on its own individual worksheet. I would like to use VBA to apply colours to individual series based on the name of that series. I have applied the following code which I found on the following help forum, changing it slightly to fit my needs:

View Replies!   View Related
Stacked Column Chart (tying Up The Loose Ends)
If the user adds 3 new lines, to the data table, – the Series Value Ranges should increase by 3 lines – AUTOMATICALLY(!) to avoid the user to full-around with the chart. It should take care of the 4 Series Ranges and also of the Range for the X-Axis Category Labels. As far as I recall, the way to accomplish it, is by NAMING the ranges with COUNTA etcc BUT, here – the last row is an empty rowc

View Replies!   View Related
Text To Columns :: Macro To Split Text
I am trying to write a micro code to split text which is copied into cell A1 into columns. I can do this fine by going to "data" the "text to Columns" and selecting the places i want to split the text (this is the same for every piece of data i copy in).

The macro works perfectly every time. the problem is that the spreadsheet is shared and i want to protect certain cells on the sheet, when i protect the sheet the recorded macro does not work as the "data", "text to columns" is not available in a protected workbook.

I was just wondering if someone could help me, so i can run a macro to split the text which also allows me to protect cells. In the "text to column" option the "fixed width" (column breaks) i choose are: 4, 25, 34 and 43.

View Replies!   View Related
Text To Column - Want Columns Formated To Text
In Column A1:A10 I have a really long series of alpha numberic digits in each cell.

I use this macro with text to column to split them up for me into different columns.

The problem I have is that after they go through this conversion all of the fractions in columns L are turned into dates....

View Replies!   View Related
Text In One Column But NOT By Text To Columns
I've had this issue a couple of times and can't work out an easy way to deal with it.

I have text data in one column.


How do I extract Row 1 into Column 1, R2-C2,... R7-C1, R8-C2?

To make it more tricky what if there isn't a consistent amount of data, ie sometimes I'll have Manager name (6 rows of data) and sometimes I won't (5 rows of data) and then the next collection of data will have it again.

Does this make sense?

View Replies!   View Related
A Better Way Than Text To Columns
Is there a better way to separate one column into two, other than "Text To Columns" I am trying to separate a 'Chair Logic' from one comma or hyphen and don't want to separate it from the other. (ie. F335-2743,XH-4,S0-24 to F335-2743 | XH-4,S0-24) and (C-24-11-1T7-TRFJ-TRF-TRLE to C-24-11 | 1T7-TRFJ-TRF-TRLE).

View Replies!   View Related
Text To Columns - VBA?
If I run text to columns from the data menu, it works just fine. Every cell becomes a date and is right justified.

However, if I record a macro, some cells fail to convert whilst others do.

My ....

View Replies!   View Related
Text To Columns
I need to seperate text to columns. How can I have it look at a cell and only put text to column if it is seperated by more than 3 spaces. my data in cell A1 looks like this.

John Smith Sue Smith Frank J. Smith

Text to columns gives me 7 columns when I only need 3

View Replies!   View Related
Text To Columns Function
I have a list of items with a cell, however these are on seperate lines (using Alt+Ent) function. Example of entered text within the cell below:


The beginning of each line will always start with an underscore ( _ ). The items within the list will either be 2 or 3 characters long (which includes the underscore).

The required output I'd like is (spaces used to indicate seperate cells):

_UN _OD _PN _H

I'm trying to use the 'Text to Column' function to solve my problem, however I haven't yet managed to get it to work. I've tried using the 'Fixed Width' function within this, however when I use this, it inserts an 'Enter' within the cell, which I don't want.

Does anyone else have a solution? Any help would be appreciated. Preferably I'd like this to be automatic using a formula, instead of me having to click the 'Text to Column' button each time.

(I'm using Excel 2007 if this makes any difference too)

View Replies!   View Related
Text To Columns Macro
First of all i have to mention that i'm not a coder guy so excuse me if i'm no good in explaining the problem.

What i'm trying is to convert texts into columns. I constantly have an email containing short codes about my business and each code has a special meaning. So i need to convert these codes and lookup in my database. Here is an example of the source file in email.

V * AIRFLPAC * EC-REG * L405A71 * R690A71 * LNGTUR
V * ESH-LEFT * UDFP * 4*2 * TRACTOR CONC-BAS............

View Replies!   View Related
Separating TEXT Into Different Columns
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 Replies!   View Related
Text-to-Columns Macro
I am using the TexttoColumns macro to split cell contents into two columns. It works well except for one condition where the data it is parsing has a trailing zero. Excel drops the zero but I need it in the output.

Example Sun Management Center Agent: 4.0

Column A Sun Management Center Agent
Column B 4

View Replies!   View Related
Concatenate Two Columns Of Text
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.

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