#1   Report Post  
Beth
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Beth
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Format Questions Mestrella31 Excel Discussion (Misc queries) 2 January 17th 05 09:36 PM
Questions about MS Word [email protected] Excel Discussion (Misc queries) 5 January 13th 05 04:49 AM
SPAM in Excel General Questions Carole O Excel Discussion (Misc queries) 1 January 7th 05 09:04 PM
A few VBA questions - long post! Fiona O'Grady Excel Discussion (Misc queries) 5 December 19th 04 05:12 PM
Find & Replace questions David Excel Worksheet Functions 1 November 2nd 04 07:41 PM


All times are GMT +1. The time now is 12:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"