Free Microsoft Excel 2013 Quick Reference

Excel convert 1 row 5 words INTO 1 row 5 columns

I have a document that was scanned, converted to Word and now I need it in
excel to have the right formatting. I need to get it back to a format so I
can manipulate it for formulas. Example:

1 row with five words and numbers with a seperation by one space only " ",
needs to be 1 row and five individual columns. Is this possible and if so
how do I accomplish this?

Thanks! respond to kmangus@floorpartners.com please.

(Summary: items from row 1 and columns A-G were scanned to a document.
Unfortunately those items pasted into column A only. Therefore seperation
between words and numbers need to be converted. Is this possible?)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions


Select the cell, then do Data|Text to Columns|Delimited|Space|Finish

--
I have a document that was scanned, converted to Word and now I need it in
> excel to have the right formatting. I need to get it back to a format so I
> can manipulate it for formulas. Example:
>
> 1 row with five words and numbers with a seperation by one space only " ",
> needs to be 1 row and five individual columns. Is this possible and if so
> how do I accomplish this?
>
> Thanks! respond to
> (Summary: items from row 1 and columns A-G were scanned to a document.
> Unfortunately those items pasted into column A only. Therefore seperation
> between words and numbers need to be converted. Is this possible?)
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...et.f unctions

Hi,

My spreadsheet has 4 columns of data but I need to convert just the 1 column
into a row.

Example:

Column A has the date
Column B has the 30 minute intervals starting from 09:00 to 17:00
Column C has the 5 different stocks (ABL, AGL; ANG; BAW;SLM) I want data for
but each stock is listed multiple time per interval
Column D has the actual values of the stocks at the diffferent intervals

I need to convert column C into 5 columns but retain the other columns data
applicable to the various stocks so its easy to view in a table-like format.

Using copy/paste/tranpose feauture in Excel is not my solution.

Please help.

Thanks
Mohoney

I want to convert a row of financial data for individuals into a column. For
example, convert:
Name Jan 2/05 Jan 9/05 Jan 16/05
....... Total
John Smith $5.00 $10.00 $15.00
$30.00

to:
Name John Smith
Jan 2/05 $5.00
Jan 9/05 $10.00
Jan 16/05 $15.00
..
..
..
..
Total $30.00

Can anyone help?

If you have information in Row 1 e.g

a b c d e f g
1 CF513 GT636 ER254 GH564 ER543 BG215 FG232

Is there any quick way of converting that row of information into a column e,g.

A
1 CF513
2 GT636
3 ER254
4 GH564
5 ER543
6 BG215
7 FG232

is there a method in excel where I can convert a row of entires into a column

example...
can this -->
Population 10 20 30 40 50 60

be converted into this
Population
10
20
30
40
50
60

thanks

I need help with a macro. I have a spreadsheet that contains 3 columns. Column A is the date data was entered into the row. Column B is a web URL. And Column C is a 1 or 2 word description of what type of URL column B is (ie video, image, etc.) Currently I have to fill in column C myself but it would be much easier if Column C were to be autofilled based upon certain keywords. For example...the keyword being 'youtube' in column B would automatically put 'video' in column C. Is this possible? Any help would be greatly appreciated.

When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.

Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?

Anyone have any ideas on how to make this happen?

Hi,

I have whole set of data. It has the following pattern
-------Column A...Column B.
Row 1: Company
Row 2: Location

What I would like to do is make it so that row 2 goes into column B..So that I have

Row 1: Company --- Location
Row 2: Company --- Location.

I know this is possible, but I just can't figure it out..

Please help!

Hello

Is there any way to convert multiple rows to a single column?

Example:
Row 1: 1 2 3 4 5
Row 2: 6 7 8 9 10

To get final result:
1
2
3
4
5
6
7
8
9
10

Thank You

Hi
Quite often I use VBA code to autofill some formulas and normally it works more or less ok. But I'm having a problem with autofill down to populate value "1" from G2 as far as the rows go in column A.The code that I currently have is as below:

Sub autofill_down()
    Range("G2").ClearContents
    Range("G2").Value = "1"
    Range("G2").Select
    Dim K2 As Long
    K2 = Cells(Rows.Count, "A").End(xlUp).Row
    Range("G2:G" & K2).FillDown
End Sub

If there is some information in col A further than A2 then its not a problem and the code populates as below:

-------------- -------------- Col A ------------------------------------------------ Col G
-------------- --------- Header text 1 ----------------------------------------- Header text 7
Row 1 -------------- Text 1 ------------------------------------------------------ 1
Row 2 -------------- Text 2 ------------------------------------------------------ 1
Row 3 -------------- Text 3 ------------------------------------------------------ 1
Row 4 -------------- Text 3 ------------------------------------------------------ 1
Row 5 -------------- Text 4 ------------------------------------------------------ 1
Row 6 -------------- Text 5 ------------------------------------------------------ 1

The problem is when there is a information only on one line (e.i. only on A2 and not any further). if this is the scenario it populates the value 1 in G2, but as soon as the autofilldown kicks in, for some reason it copies the line above. Please see below.

-------------- -------------- Col A ------------------------------------------------ Col G
-------------- --------- Header text 1 ----------------------------------------- Header text 7
Row 1 -------------- Text 1 --------------------------------------------------- Header text 7
Row 2 -------------- ---------- --------------------------------------------- ----------
Row 3 -------------- ---------- --------------------------------------------- ----------
Row 4 -------------- ---------- --------------------------------------------- ----------
Row 5 -------------- ---------- --------------------------------------------- ----------
Row 6 -------------- ---------- --------------------------------------------- ----------

Is there any way to have a code that would populate value 1 as per Column A but would not have this sort of drawback.

Any ideas?

I have a great deal of data arranged like so:

Name : John Doe
Location: MIAMI, Florida 33193-2840, USA
E-Mail: Johndoe@hotmail.com
Primary Specialization Field:
Secondary Specialization Field:
Address Line 1: 555 Smith St
Address Line 2:
Address Line 3:
Telephone Number 555555555
Fax Number
Expiration Date February, 2009

Name : Jane Doe
Location: MIAMI, Florida 33193-2840, USA
E-Mail: Janedoe@hotmail.com
Primary Specialization Field:
Secondary Specialization Field:
Address Line 1: 555 Smith St
Address Line 2:
Address Line 3:
Telephone Number 555555555
Fax Number
Expiration Date February, 2009

I want to change the rows of information into columns of data. "Transpose" and then collate so It would look like this if delineated by comma:

Name,Location,Email,etc
John Doe,MIAMI, Florida 33193-2840, USA,Johndoe@hotmail.com,
Jane Doe,MIAMI, Florida 33193-2840, USA,Janedoe@hotmail.com,

Clearly it doesnt have to be in .CSV format, but I would like some help on this. Note: I am in no way VBA proficient, I would like to do this within excel, its tools, and functions.

Thank you for your help

Note: not all forms have all data. For instance, if no email is entered then there is no email field.

Hello all,

I have a bunch of data in which I need to have certain columns moved down 1 row, and then moved to the left 6 columns.

I figured it out to the point where there is a blank row inserted after every single row, but I can't seem to figure out a quick way to move the needed columns over without overwriting data.

Attached is an example of what I am trying to do. Hopefully someone can understand what I am looking for. I am willing to pay for a solution, if needed.

Thanks for your time!

Dear Excel Users.

I would like to convert multiple rows to single continous column.

I have attached a cpoy of my data for your reference.

Kindly advice.

Many Thanks
BAttachment 133246alaji

I want to sort 2 rows based on a value in 1 row, in a different column.

The spreadsheet entries look something like this:

1034 -----1----------$4.48
-----------------------($1.38)
---------------------------------
500091---1---------$10.49
----------------------($2.28)

I want to sort the spreadhsheet based on the left-hand column, but I want the ($1.38) to stay with the $4.48, and the ($2.28) to stay with the $10.49.

If I just sort on the left-hand column, the numbers with parentheses all sort to the bottom because the left-hand column is blank in those rows.

The application is to my seller account on amazon.com. This is the spreadsheet I download of items that have been sold. The numbers in the left-hand column are SKUs that I use to identify the books I'm selling. The $4.48 is how much the customer paid. The ($1.38) is how much Amazon deducted for fees. I want to sort by SKU but I need both the customer's cost and Amazon's fees for a particular book to stay together.

Here's a screen shot of part of the actual spreadsheet, which might make it clearer:

http://www.rockisland.com/~irthlingz..._transactions/

Thanks!
Mike

Hello All,

This is my first post here and looking for help.
I am working with a database which does not let me import data. So i have taken screen shots of the data, cropped the relevant data needed using MS paint and pasted the images on a word document.Next converted the word Doc into PDF and then using a OCR, have converted the images to editable text. Next have copied the converted text into excel.

Now I need help formatting the data. Have succeeded in separating the single column into multiple columns using the de-limited format and removed the parenthesis using replace function

Need help on the following

1) Converting the multiple rows of data in each paragraph in MS word into a single column
2) Pasting the different paragraphs in the word document into different columns.

Any and all help will be much appreciated. Am attaching the files for clarification.

Thank you very much

Suresh

I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.

I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.

Good Afternoon Everyone,

I'm currently faced with a spreadsheet that has data formatted like this:
A
1 RandomRowofData1
2 RandomRowofData2
3 RandomRowofData3
4 RandomRowofData4
5 RandomRowofData5
6 RandomRowofData6
7 RandomRowofData7
8 RandomRowofData8
9 RandomRowofData9

Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words)....

I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.

Make sense? I tried doing an OFFSET formula that I found on the support.microsoft website that "sorta" worked...but the formula was written for 4 rows of data and I'm having trouble personlizing it to suit my needs. Ideally, I'd like to add column headers from C1:K1 and paste the formula from cell C2 to K2 and down.

The formula in question is:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

Go easy on me...I'm pretty much self taught with my excel knowledge and the way I usually learn complex formulas is by applying them to my needs and dissecting them.

...sorry this post is so long, but any help is GREATLY appreciated!!

Thanks,

Laura (complex formula noob)

P.S. I have attached an example of the scenario I'm dealing with. Sheet1 shows the "before" and Sheet2 shows what I want my "after" to look like.

I have a worksheet which has numbers 0 and 1 in it. Its spread over 3 columns what im trying to do is check through all 3 columns of data and if all 3 columns have a 0 I need that to be put in column H as a 0 and if any column has 1 in it I need that to go into column H as 1
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH1=
HIJK1010 210  3001 410  5001 6001 7001 8001 9000 10001 11100 12010 Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Looking at the above, what I mean is if any data in row 1 for instance has 1 I need that shown in column H but if any data has a O (like in row 9) I need that shown in column H. I need it to check all the columns.

Can anyone help. Hope not too confusing

trying to split data from 1 row into two separate rows, then copy and paste
the function(s) without skipping rows. i don't know how to word it better,
but here is my problem:

a b c d e f g h i j k
1 xyz 200 5.8 xyz 200 5.2 xyz 200 5.8
2 abc 400 10.6 abc 400 10.1 xyz 200 5.2
3 lmn 300 4.5 lmn 300 4.8 abc 400 10.6
4 qrs 500 8.7 qrs 500 9.5 abc 400 10.1
....

i want to copy data from row "1" from columns "a:c" into row "1" columns
"i:k" and then copy data from row "1" from columns "e:g" into row "2" columns
"i:k"...

finally, i would like to copy and paste the "i:k" columns downwards.

when i tried to do this with copy and paste, it would skip the data from
every other row.

sorry, i can't be more clear. hope someone can help. thanks in advance.

I am importing a table into Excel. The table is text and has columns which
include fractions such as 1/8, 3/16, 5/32, and also composite fractions such
as "1 1/8", "2 3/4" (but without the quotes).

Excel thinks these are dates and autoconverts them to a date serial number.
Once this happens there is no way to recover the original fractions.

I have tried preformatting the columns where the data will end up as
'Fraction' format, and 'Text' format but that makes no difference.

Is there a simple way of stopping this behaviour? At present I am manually
pre-processing the data in Word to put quote marks around the fractions so
they are imported into excel as text. I can then use formulas to re-create
the deired numerical fractions.

Hi All,

I need some help with converting my excel data from multiple rows to
multiple columns. Say i have a long list of serial numbers in a single
column, like this:

10000
10001
10002
10003
10004
10005
10006
.....
19999

How can i convert it to columns of data, like this?

10000 10005 10010 .....
10001 10006 10011 ....
10002 10007 10012 ....
10003 10008 10013 ....
10004 10009 10014 ....

I need each column having a specific numbers of rows, say 40, before
the data continues to the next column. Is there any functions other
than Macro programming?

Thanks in advance!

Hi,

I have come across a problem to import data from a text file into excel. I have previously used software called Monarch Pro from Datawatch which was magical and converting data from text to excel was a breeze but unfortunately I do not have access to that software anymore. I shall try to explain what I want to do. I have hope that one of the brilliant brains in this community will be able to come up with a solution.

I have a text file with some headers (in rows) and then data in columns. The text file has page breaks. So if some data specific to a particular header is large, it will appear in multiple pages. Each page will repeat the headers. In case of less data, the next set of data appears in the next page. Please refer to the attached files.

What I am trying to achieve is that when I import that data into excel, the currency and account number (Headers) should become part of the data in columns.

The currency and account number will change as the headers change in text file.

Please note that the data is very huge. So it is almost impossible to import the data through “Import External Data” option and then manually insert columns and add the headers into columns. I’ll be grateful if this can be achieved through a macro.

PS: The calculation in the enclosed text file is not correct because I just copied and pasted the data without calculating the correct balance in the last column.

Hi.

I will try my best to explain my problem.

I have data in the following format:
Column A - contains the expenditure type
Column B - contains the expenditure amount for Department A
Column C - contains the expenditure amount for Department B
Column D - contains the expenditure amount for Department C
The rows for the above table could vary from month to month.

How could Excel populate the pivottable data above into the following format in another worksheet:

1. Data from column A to be populated into Column A starting from Row 1
2. Data from column B to be populated into Column B starting from Row 1
3. Data from column A to be populated again into Column A starting from the Row immediately following the last populated Row in step (1) above
4. Data from column C to be populated again into Column B starting from the Row immediately following the last populated Row in step (2) above
5. Data from column A to be populated again into Column A starting from the Row immediately following the last populated Row in step (3) above
6. 4. Data from column D to be populated again into Column B starting from the Row immediately following the last populated Row in step (4) above

Thanks.