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

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

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

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