function to divide data in one cell, into two cells

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I know I can use the "text to column" in data to separate
data in one cell into two cells, but is there a way of
automating this process by building a formula so that when
I copy and paste a report into an excel spreadsheet it
will automatically separate data in one column into two
columns.
Doug
 
Hi Doug..........

There are a number of formulas that can accomplish what you're looking for
but they each depend on the exact structure of the data you're importing,
and depend on it being the same every time, for example first and last names
separated by a space, or number groups separated by a hyphen, etc
etc........maybe post some examples of your data and someone might can offer
a few suggestions........


Vaya con Dios,
Chuck, CABGx3
 
Hi Chuck,
It will be the same structure each time. I get a report
each day that contains a column with both the date and
time in one cell, separated by a space.

Example: 6/27/04 20:00

It has a general format and I would like to create a
formula that puts the date and time in two different cells
when I paste the report into the excel spreadsheet.
Thanks for your help,
Doug
 
There are probably shorter versions but this works IF the cell containing
the date/time is something Excel recognizes as date/time.

=DATE(YEAR(A1),MONTH(A1),DAY(A1))
=TIME(HOUR(A1),MINUTE(A1),)
 
Or, in cell B1: =INT(A1)
and in cell C1: =A1-B1

Format B1 as a date, and C1 as Time
 
Or in C1

=MOD(A1,1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Hi Doug............

That being the case, you could just put =A1 in both columns B and C and copy
down or just highlight column A and drag it to the right, essentially just
copying column A over to B and C.............and then just simply reformat
columns B and C to display the date as you wish, and the time as you
wish........

Vaya con Dios,
Chuck, CABGx3
 
This is a perfect example of why we ask you, DOUG, to stay in the same
thread.
Peo and Debra are *duplicating* the suggestions that you have already
received in your original post from J.E., and Biff, and others.

YOU are wasting other peoples time!

And now I see that you've started another post on the same subject!

Have some consideration for the folks here who volunteer their time.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi Chuck,
It will be the same structure each time. I get a report
each day that contains a column with both the date and
time in one cell, separated by a space.

Example: 6/27/04 20:00

It has a general format and I would like to create a
formula that puts the date and time in two different cells
when I paste the report into the excel spreadsheet.
Thanks for your help,
Doug
 
Back
Top