2 Questions

[Beth]

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

 

[Frank Kabel]

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]

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]

Hi
you have to format the resulting cell with
[hh]:mm