Re: Need some help with excel spreadsheet...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dave Peterson (ec35720_at_netscapeXSPAM.com)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 17:46:46 -0600

The Data|Text to columns was an alternative to the formula approach (along with
the copy|paste special values)

But if you select that column, then do Data|Text to columns, you'll see that
wizard pop up to help.

On the first step (1 of 3), you'll be prompted to find out if your data is Fixed
Width or delimited (like with tabs or commas).

You'd choose Fixed width (then Next to step 2)

This is the screen where excel will guess at your columns. If you don't like
its guesses, you can "draw lines" where you want. In your case, you'd only want
one line drawn.

If excel guessed incorrectly, just double click on the existing line(s) to
remove them.

But you'd want a line between the first and second characters.

1YYMMDD
would look (sort of) like:

 |
1|yymmdd
 |

Then click the next button.

For the first field (that single column), you'd choose "Do not import column
(skip)".

For the second field, you'd choose Date ymd.

Then finish it up.

And format that column to show the date the way you like.

If you try this against a test work***, I think you'll find it the quickest
way to convert that kind of data to dates.

warpman wrote:
>
> Hi Dave. Thanks so much for your info.
> I think I'm a little confuse about the "draw a line after a 1" I'm not
> sure what exactly are you talking about.
>
> The first thing I would like to do is change the date.
> The second thing is the subtotals.
>
> On the first one I found the MID function to work. Using the MID
> function I was able to break down the data and get the correct format on
> a separate column. I used the following formula:
> =MID(C1,4,2)&"/"&MID(C1,6,2)&"/"&MID(C1,2,2)
>
> After I got this on the first working for the first row I did a special
> paste for the rest of the rows and it worked!
>
> Now I got to work on the second issue. I'm going to try your suggestion.
> I would let you know if this is going to work. Thanks again.
>
> Dave Peterson wrote:
> > I think I'd convert those 1yymmdd strings to real dates.
> >
> > Select that range that has those values.
> > data|text to values
> > draw a line after the 1
> > Make sure you ignore that field (do not import)
> > and select ymd for the "real" field
> > Plop it right back where you got it.
> >
> > Then format|cells|number tab|custom category:
> > In the type box:
> > mmddyy
> >
> > The values will really be dates--just formatted to look like you want.
> >
> > And I'm guessing that you already sorted your range and did Data|subtotals to
> > get those subtotals.
> >
> > But for some reason, those values aren't really numbers. Either they were
> > imported as text or they have some extra characters in those cells that make the
> > values non-numeric.
> >
> > =isnumber(a1)
> > would be one way to tell.
> >
> > A quick way to convert text numbers to number numbers...
> > Select an empty cell
> > edit|copy
> > select your offending range
> > edit|paste special|check that Add button
> >
> > If that doesn't work--and it may not if your data came from a web page (HTML
> > non-breaking spaces are a problem).
> >
> > You can use David McRitchie's Trimall subroutine to clean up that range.
> > http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> > (look for "Sub Trimall()")
> >
> > And if you're new to macros, you may want to read David's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > warpman wrote:
> >
> >>I'm running excel 2003 on a winxp pro machine.
> >>
> >>I have a spread*** with the following information.
> >>
> >>Cust Number, Cust Name, Inv Num, Inv Date, Description, Amount
> >>
> >>There are many invocices per customer number. The date appears as
> >>1YYMMDD.
> >>There are a couple of things I would like to Change. I want to change
> >>the date to MMDDYY format. I want to add the amount on the invoices
> >>and have the following per customer numnber: Cust Number, Cust Name
> >>and a Total amount from the invoice amount. Instead of having one 5
> >>lines for 5 invoices for 1 customer number I want to have 1 customer
> >>number an a grand total on the invoices.
> >>I have been able to run the subtotals on the amount colunm but it only
> >>shows Customer Num Total $00000.00 (as an example). It also shows the
> >>other rows with the information. I need to press the + or - sign to
> >>open or close the rest of the rows. I don't want this.
> >>I have not been able to find the substring function to break the date
> >>field or may be I have not been looking in the right location.
> >>Any help on this would be greatly appreciated.
> >
> > 

-- 
Dave Peterson
Quantcast