How to give a string of data into different columns
If I have cell containing a series of data like this ....
Is it possible to use function or command to split them into columns?
1 12 9
1 2 9
1 22 17 18
23 23 1 9
24 21 1
1 23 11
I don't want to use "TEXT TO COLUMN" as some of the addresses can be
Thanks so much,
Hi 'Elton Law'
Try the below
=TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",",
REPT(CHAR(32),255)),COLUMNS($B$1:...How can I make columns that are separated by a line?
I need to make columns that are separated by a line. Is there a function on
the toolbar that will do this?
Insert a column between A and B
Make it very narrow and draw a line from Drawing toolbar down the center of
Or place a border down left side of cells of Column B
Or post back with more detail.
But no.............there is no toolbar function to separate adjacent
Gord Dibben MS Excel MVP
On Fri, 29 May 2009 14:17:01 -0700, AdaptableAssistant
>I need to make columns that are separated by a line. Is there ...Passing info into a text field when doing the update query
I want a text boxt pop up in order to pass the information . The pop up
will say : Notes taken by:
I want it to do in one go
This is my update query:
INSERT INTO Catatan ( NO_URTANGT, TGLCATATAN, WKTCATAT, Subject, Notes )
SELECT a.NO_URTANGT, a.TGLCATATAN, a.WKTCATAT, a.Subject, a.Notes
FROM [C:\Churchdata\Churchdataconso\BkEnd\Hahomion_be.mdb].Catatan AS a;
Thanks for any help.
Separate field for NotesBy:
INSERT INTO Catatan ( NO_URTANGT, TGLCATATAN, WKTCATAT, Subject, Notes, NotesBy )
SELECT a.NO_URTANGT, a.TGLCATATAN, a.WKTCATAT, a.Sub...Unlocking A Grid For Moving Text Around In A Chart: How, Please ?
Using MS Excel 97 SR-1
Guess this dates me pretty well,m but it does most everything I want.
When making a graph, I sometimes put text notes in the graph.
The text seems to be locking itself to a pre-ordained Grid.
Is there any way to eliminate this grid locking, so I can move the text
smoothly to where I want it ?
Not sure if it is in Excel 97, but on the drawing toolbar in later versions
there is an option 'snap to grid'. see if this helps
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get
Ice Hocke...Column sorting incorrectly
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?
Are you absolutely sure of the format? Sounds like the errant values are text.
Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank rows
To be sure, select the values and then sort.
Regards;...Forumla rows and columns
Why in a column do I get a (0) result from a formula in one cell in the
column and get a - for a result in the same column, different row with the
same formula for each, the format for the entire column is accounting. I
want all zero results in the column to be displayed as a dash (two results
are (0) in the column and two other results to the same forumla show as the
dash which is the correct format for accounting) -- Help !!!!
The formula result is between 0.5 and -0.5. Formatting does not alter the
real value. Wrap ROUND around your formula:
...Text width in custom MessageBox Class
I�ve implemented my own MessageBox class in order to display custom icons.
Everything works fine,
the only problem I have is to determine how much space the text will need. I
know about methods like DrawText(DT_CALCRECT) and so on but therefore I need
a DC which I don�t have at that point, as I have to define the window rect
before the window is actually created.
I have also considered using the DC of the parent�s window but that seems a
bit odd to me.
What�s the regular way to handle this problem?
Thanks for your help
Try calling the GetDC() API to get a HDC...Hidden Columns
Cannot open hidden columns by selecting the column adjacent to either side
Is the worksheet protected (check Tools/Protection on the menu bar)?
Rick (MVP - Excel)
"FITZWALLY" <FITZWALLY@discussions.microsoft.com> wrote in message
> Cannot open hidden columns by selecting the column adjacent to either side
...Export from 2000 to 2008
I did an export of data from 2000 to 2008, all the tables and data copied
over with the exception of the Identity attribute. (which is the auto number
This is not good, I tried to manually do it and it says that I need to drop
and recreate table. This poses an issue given all data I have in the table
and that needs to be imported over.
What am I missing?
MOCK (MOCK@discussions.microsoft.com) writes:
> I did an export of data from 2000 to 2008, all the tables and data
> copied over with the exception of the Identity attribute. (which is the
> auto number fe...How do I Chart a spreadsheet that has multiple columns of data
I have a spreadsheet that has multiple columns of data. What I have been
asked to do is take the 6 categories and get the actual and standard data in
Any help would be most apprciated.
Did you try selecting the data and running the chart wizard?
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
"soconfused" <firstname.lastname@example.org> wrote in message
>I have a spreadsheet that has multip...What is controltip text? Looking for a field to store some text
I need a field in the properties to store a descriptive name of a textbox's
If a required field, which has "R" in the Tag field in the properties, has
not been entered a message box appears:
MsgBox "Please enter a value for " & ctl.Name
Where ctl.Name is the name of the text box. l need to be able to say, for
example, "Department Number" instead of "txtDeptno". I could put
"Department Number" after the "R" in the tag and parse up tag but is there
another field? How about ControlTip Text or Validation Text?...column reference
I want to take the average of every other column in my data set and I will be
adding weeks to the data as time moves forward. Is there a relative or
absolute formula that does this?
Thanks for any help.
To average the odd columns:
Both of these are array formulas, and should be confirmed using
Ctrl+Shift+Enter, not just Enter.
*Remember to click "yes" if this post helped you!*
> I ...data in 1 column/cell to automatically repeat in another cell/col
i am adding clients names in colmn B and want that info to automatically
repeat in column Q. Anyone know the formula
in cell q2
or a worksheet_change event
right click sheet tab>view code>insert this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.row < 2 Then Exit Sub
Target.Offset(, 15) = Target
Microsoft MVP Excel
"Warthog" <Warthog@discussions.microsoft.com> wrote in message
news:AF434D19-E550-48F9-81C2-E071CC33F594@microso...Copy text and graphics boxes from Publisher to Word
I need to copy a sinigle column brochure from Publisher to Word.
When I select all the boxes and copy, then open Word and paste, nothing
When I try to save the file as a Word document (in Publisher), I get an
error message telling me it can't be done because the document contains
I can save the file as a pdf and then save the pdf to Word, but I don't get
all of the graphics boxes. I need to send the file to someone and they
don't have access to Publisher to edit it.
Any help appreciated. Thanks.
Copy and paste will work.
Jo...Changing color of text based on formula result
It' tough getting old. Can someone please refresh my
memory as to how you change the color of a cell's contents
based on the result of the formula? Thanks.
Conditional Formatting is what you're after.
Take a look here for examples:
> It' tough getting old. Can someone please refresh my
> memory as to how you change the color of a cell's contents
> based on the result of the formula? Thanks.
...editing text within a cell before moving to a new cell
Does anyone know how to move back and forth through text WITHIN a cell
when first entering text in that cell? (If this is unclear, it's
because I'm not sure quite the best way to explain it). Basically,
imagine I type two words into a cell and realize I've mispelled the
first one. If I hit the left arrow key (ostensibly to move back to the
first word) excel will move me back an entire cell. I then have to (1)
right-arrow over to the original cell, (2) hit F2, and (3) then scroll
back to the misspelled word.
Is there any key modifier I can use to skip directly to (3) without
...line spacing w/in a text box
I'm having difficulty with line spacing within a
textbox. I go to the Format menu and click line spacing
and even if I adjust between lines and before & after
paragraphs I still have way too much space between the
lines. I'm designing a business card and am having to
use a separate text box for each line on the card. I'd
like to have just one text box for everytthing on the
card. Can anybody help? THANKS!
> I'm having difficulty with line spacing within a
> textbox. I go to the Format menu and click line spacing
> and even if I adjust...Excel How do i make a range of cells proper text?
I have created an excell spreadsheet from Peachtree accounting. all the
customer names are all caps. i want to select the whole worksheet and use a
formula to make the whole sheet "proper" which will make the first letter of
each work capitlaized and the rest of the work lower case. please be
specific. i am not an excell expert , just a business owner ttrying to get
by. Thanks, peter
Hi, Peter. Try this:
There are several ways, and you can choose which one's best depending on how ...Seeing Text File Names when Saving Excel Files
I am new to Excel 2007.
I am saving a new Excel file and I want to give it a similar name to an
existing text file so that I know by the filename that the two files are
related to the same project.
However, when I "Save As" the Excel file in the selected directory, the only
other files I see in that directory are the other Excel files. I don't see
any of the other files that I know are in there (word, powerpoint, pdf, etc.).
In prior versions of Excel, I have been able to see all of the files in a
destination directory....is that still possible with the 2007 version of
...Can't get text string to recognize query result
Private Sub cmdConfirm_Click()
Dim strMsg As String
If Me.cboApproval = "Approved" Then
strMsg = "Your meeting request has been approved." & (Chr(13))
strMsg = strMsg & "Meeting Date: " & Format([txtFTDate], "mm/dd/yyyy") &
strMsg = strMsg & "Meeting Start: " & Format([txtFTStart], "h:mm AMPM") &
strMsg = strMsg & "Meeting End: " & Format([txtFTEnd], "h:mm AMPM") &
strMsg = strMsg & "Meeting Location: " & [txtFTBldg] &a...Change Column Name
Can anybody advice me on how to go about change the column
name in the worksheet. Instead of currently the column
name is A, B, C I would like to change to for example
A="Date" , B ="Name", and so forth to be shown on the
It can't be done.
You can change it to "1, 2, 3, etc." as opposed to "A,B,C, etc"
by selecting R1C1 reference style.
Use column headers in the first row.
If you want, you can get rid of the row and column headings
altogether (meaning that your first row/header row will be all that...Query: convert multiple logical fields into single 2 character text result
I've solved this particular puzzle, although I'd like to find a better
solution than mine. So my emergency is resolved, nevertheless if you like
puzzles, please read on...
The school district wants a CSV file with fields in a specific order and
with specific formatting. Here are the seven possible results for EthnicCd:
2 Char where
ML = Multiple Races
UN = Unknown or Declined to Report
IN = American Indian or Alaskan Native
AS = Asian or Pacific Islander
BL = African American
HI = Hispanic
WH = White
Our "People" database contains eight ethnicity Yes/No...How do I eliminate automatic appearance of paragraph signs?
When I type in my text, automatically I get the paragraph sign appearing. At
the same time, instead of a space between words, there is a dot that appears,
not a period, but a dot, halfway up the line! How do I get back to standard
typing? This is driving me crazy!!!! email email@example.com
"In typing don't want dots in spaces!!!!" <In typing don't want dots in
firstname.lastname@example.org> wrote in message
> When I type in my text, automatically I get the paragraph sign appea...Needed: Chart that combines clustered column and stacked column types
The chart is to show projected financials for a business.
- Using Clustered Columns, I can show Revenue and Net Income. Not a
- Using Stacked Column, I can show the contribution to Revenue from
each of the businesses two product lines. Not a problem
What I would like to do is to combine these into a single chart: Show
a Stacked Column with the Revenue contributions and next to it
(Clustered Column) show Net Income.
Is this possible? If so, can you please explain how to do this.
Posting a simple example would be a bonus.
gerry <at> pairofdocs <dot> net
O...Error number of columns does not match the header row! on Import
I am trying to import data from GoldMine into CRM and getting Error number
of columns does not match the header row! can anybody help
Many thanks in advance
how are you importingthem are you using scribe, or a diffferent tool
> I am trying to import data from GoldMine into CRM and getting Error number
> of columns does not match the header row! can anybody help
> Many thanks in advance
Check the thread of 20/3/2007 "Number of columns does not match the header
row" I think it'l...