Home |
Search |
Today's Posts |
#1
|
|||
|
|||
2 Questions
Hello and Seasons greetings
I am importing a CSV into Excel and have come across 2 questions: 1 The spreadsheet is for transactions and there maybe more than one line for a transaction. If there is more than one line there is some of the data that is ommitted (Its the same as the first line for that transaction). I need to copy the data from the first line into the second line. To further complicate I need to concatenate 3 of these cells together, into a helper cell. How do I do this? 2 One of the cells is Date/Time in the format yyyymmddhhmmES with no delimiting characters. How can I convert this cell to mm/dd/yyyy hh:mm and truncate the time zone? Thanks for your help Beth |
#2
|
|||
|
|||
Hi
for the first one you may provide some more information (e.g. some example rows of data). for the second question you may try the following formula in a helper cell: =TEXT(--LEFT(A1,8),"0000-00-00")+TEXT(--MID(A1,9,4),"00\:00") "Beth" wrote: Hello and Seasons greetings I am importing a CSV into Excel and have come across 2 questions: 1 The spreadsheet is for transactions and there maybe more than one line for a transaction. If there is more than one line there is some of the data that is ommitted (Its the same as the first line for that transaction). I need to copy the data from the first line into the second line. To further complicate I need to concatenate 3 of these cells together, into a helper cell. How do I do this? 2 One of the cells is Date/Time in the format yyyymmddhhmmES with no delimiting characters. How can I convert this cell to mm/dd/yyyy hh:mm and truncate the time zone? Thanks for your help Beth |
#3
|
|||
|
|||
Thanks! Frank
Ive got that part working and I have used it on 2 cells and in subtracting them to get a time lapse I cant get the answer to come out right. I know there is a multiplier but I cant think of it. My formula: (TEXT(--LEFT(N2,8),"0000-00-00")+TEXT(--MID(N2,9,4),"00\:00"))-(TEXT(--LEFT(M2,8),"0000-00-00")+TEXT(--MID(M2,9,4),"00\:00")) Where N2 is end time and M2 is start time As for the other part of the question: A B C 1 Cat Feline Black 2 Tan 3 White 4 Horse Equine White 5 Dog Canine Yellow 6 Brown 7 Red I need a2 and a3 = cat b2 and b3 =feline b6 and b7=Dog c6 and c7 = canine I hope the formatting of the lines came out right not really sure how to do it Thanks Beth "Frank Kabel" wrote: Hi for the first one you may provide some more information (e.g. some example rows of data). for the second question you may try the following formula in a helper cell: =TEXT(--LEFT(A1,8),"0000-00-00")+TEXT(--MID(A1,9,4),"00\:00") "Beth" wrote: Hello and Seasons greetings I am importing a CSV into Excel and have come across 2 questions: 1 The spreadsheet is for transactions and there maybe more than one line for a transaction. If there is more than one line there is some of the data that is ommitted (Its the same as the first line for that transaction). I need to copy the data from the first line into the second line. To further complicate I need to concatenate 3 of these cells together, into a helper cell. How do I do this? 2 One of the cells is Date/Time in the format yyyymmddhhmmES with no delimiting characters. How can I convert this cell to mm/dd/yyyy hh:mm and truncate the time zone? Thanks for your help Beth |
#4
|
|||
|
|||
Hi
you have to format the resulting cell with [hh]:mm -- Regards Frank Kabel Frankfurt, Germany "Beth" schrieb im Newsbeitrag ... Thanks! Frank Ive got that part working and I have used it on 2 cells and in subtracting them to get a time lapse I cant get the answer to come out right. I know there is a multiplier but I cant think of it. My formula: (TEXT(--LEFT(N2,8),"0000-00-00")+TEXT(--MID(N2,9,4),"00\:00"))-(TEXT(-- LEFT(M2,8),"0000-00-00")+TEXT(--MID(M2,9,4),"00\:00")) Where N2 is end time and M2 is start time As for the other part of the question: A B C 1 Cat Feline Black 2 Tan 3 White 4 Horse Equine White 5 Dog Canine Yellow 6 Brown 7 Red I need a2 and a3 = cat b2 and b3 =feline b6 and b7=Dog c6 and c7 = canine I hope the formatting of the lines came out right not really sure how to do it Thanks Beth "Frank Kabel" wrote: Hi for the first one you may provide some more information (e.g. some example rows of data). for the second question you may try the following formula in a helper cell: =TEXT(--LEFT(A1,8),"0000-00-00")+TEXT(--MID(A1,9,4),"00\:00") "Beth" wrote: Hello and Seasons greetings I am importing a CSV into Excel and have come across 2 questions: 1 The spreadsheet is for transactions and there maybe more than one line for a transaction. If there is more than one line there is some of the data that is ommitted (Its the same as the first line for that transaction). I need to copy the data from the first line into the second line. To further complicate I need to concatenate 3 of these cells together, into a helper cell. How do I do this? 2 One of the cells is Date/Time in the format yyyymmddhhmmES with no delimiting characters. How can I convert this cell to mm/dd/yyyy hh:mm and truncate the time zone? Thanks for your help Beth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format Questions | Excel Discussion (Misc queries) | |||
Questions about MS Word | Excel Discussion (Misc queries) | |||
SPAM in Excel General Questions | Excel Discussion (Misc queries) | |||
A few VBA questions - long post! | Excel Discussion (Misc queries) | |||
Find & Replace questions | Excel Worksheet Functions |