Calling all Excel/Spreadsheet/Formula nerds

Factual Questions
1

I’m desperate and the SD hasn’t failed me yet!

Here’s the problem (BTW: I’m on a MAC, but I’m sure the formulas are the same)

As nutshell as I can do it:

                                      Import text database into Excel.
                                      each record has 4 different dates associated with it.
                                      All these dates are in the following format:
                                      YYYYMMDDHHMMSS
                                      i.e. today, right now would be:
                                      20000223090430
                                      which would read easier as:
                                      2/2/00, 9:03:30 am
                                      And ideally as:
                                      2/2/00 period

                                      Well, I have tried EVERYTHING. Attempts to change the date format result in essentially one of
                                      two things:
                                      either:
                                      ###########################################
                                      (I stretched the column width to the entire size of my scree, that has nothing to do with it,
                                      because it looks like this in the sample box in the format dialogue as well)

                                      Or something like this:
                                      2.00001E+13

                                      I have tried creating a custom format, I get the string of number signs.

                                      I have tried importing as text, as date format, as general, switching back and forth...nothing is
                                      working. There HAS to be a way, but I have not found it.

                                      Anyone who can answer this becomes my hero.

                                      And if you can tell me how to get rid of the time part of it as well you become my GOD.
                                      Thanks!

                                      Stoid
2

Well, since nobody has jumped in here yet, let me tell you how I would do it.

Since I’m no Excel expert, I’d import the whole damn spreadsheet into Access. Once there, I can tell access to truncate the field (i.e. take off the last 5 digits) then wrie a query to place the first four digits <i>behind</i> the last digits, and let it display it as a date…Let me know if you’d like to try this method, and I’ll give you more details…or e-mail me.

3

First, make sure there is an empty column to the right of the one you’re using (insert one if you need it)

Then on the menu click Data > Text to Columns
Then make sure “fixed width” is selected
Put the divider after the day, before the time.
Hit Next, and highlight the data on the left, and on the top right where it says “column data format”, choose date, and make it YMD in the drop down list
Hit finish, and the date will be in one column, the time in the other.

Trying is the first step to failure

4

I’ll jump in with a suggestion, even though it’s not using excel. I’d import the text file into access. THat way I can tell access to truncate the field…(take off the last 6 digits that represent the time) and then write a query to take the first 4 digits and move them <I>behind</i> the last number…and display the result as a date. THen you could export the whole thing into excel. There may be a way of doing it in excel, but I couldn’t find it when I looked. Let me know if you need more details, or e-mail me.

5

And even though it doesn’t look like anything is happening, never, never try to reload the page and post again…(sigh)

6

The Excel function you want is called “middle”, or perhaps “mid”, in conjunction with a datetotext function (I’m a FileMaker person more than an Excel person so I may have the function term wrong, but the principle is the same). “Middle” is a text-parsing function.

Cell A1 = 20000223090430

The result of Middle(A1, 1, 4) would be 2000
The result of Middle(A1, 5, 2) would be 02
The result of Middle(A1, 7, 2) would be 23

and so on, where you enter the cell you are referencing (A1), the starting digit in the string, and the number of digits from that point onward.

The date part would be something like

Datetotext(Middle(A1, 5, 2)&"/"&Middle(A1, 7, 2)&Middle(A1, 1, 4))

Again, this is FileMaker nomenclature, but Excel definitely can do such parsing and the terminologies are pretty close. The formula wizard should help you with the precise formula grammar.

And don’t ever import anything into Access. If you’re gonna import it into a database, go get FileMaker Pro! :slight_smile:

Disable Similes in this Post

7

I erronously typed:

That should have been:

Datetotext(Middle(A1, 5, 2) &"/"&Middle(A1, 7, 2)&"/"&Middle(A1, 1, 4))

And of course, being a Mac user, you weren’t going to import it into Access anyway, were you?

Disable Similes in this Post

8

Ahunter is mostly right, though Excel is a bit different than File Maker (unless Excel for the Mac is different than Excel for Windows.

Here is my solution.

=RIGHT(LEFT(A1,6),2)&"/"&RIGHT(LEFT(A1,8),2)&"/"&LEFT(A1,4)

This takes the left six character (200002) and then chooses the right 2 (02) then concatenates the (23) and then finally the left 4 (2000).

I hope this helps.

Jeffery

9

Hmm…yes, you wouldn’t need the texttodate part because Excel is smart about that (if the contents of a cell look like a date, Excel treats the entry as a date). So as long as you add the “/”'s, Excel would treat it as a date (this is not true for FmPro; a date field must be defined as a date field for its contents to be treated by the program as a date, and calculating dates from non-date text fields requires the texttodate function).

You (StrTrk) use a combo of right and left parsing functions, which is fine, but I assume that using middle (or is it “mid” in Excel?) would work fine in Excel as well.

Disable Similes in this Post

10

Wow, you guys sure have some complex solutions to this problem, and I REALLY appreciate you taking the itime to share them. It turns out the first one, which is also the simplest, worked perfectly! Solved my problem.

Now I have a new one!

I have records that I had to copy and paste from an HTML table, which of course screwed them up. What it left me with, after I dicked around and deleted as much as I could, was this structure in excel

Row 1) date
Row 2) data
row 3) data
row 4) blank
row 5) (THIS IS THE CRITICAL ROW - It either has a single word, say “cheese” or it is blank)
row 6) data
row 7) data
row 8) blank
row 9) blank
row 10) blank
row 11) data
row 12) blank

I have almost a thousand records structured exactly this way, 12 rows each (the work sheet is almost 18,000 rows). I want to tabulate the row 5 data, how many blank vs. how many that say “cheese” – the blanks are actually of more interest to me, unfortunately. The word “cheese” would be a snap.

We tried going to the column on the right, entering simply “=a5”, which returns either a 0 or “cheese”. Fine so far, but then how do I copy, fill, or whatever those 12 rows repeatedly down through all 1,000 records? Because I do NOT want to register anything for the other 11 rows per record.

Did that make sense?

Put another way:

18,000 rows of data, representing “records” of 12 rows per record.

I want to strip out and calculate the data in the 5th row of each 12-row record.

Line 5 is either blank or says “cheese”. I want to count the BLANKS more than I to count the words “cheese”.

HELP!