Macro or something to manip a string

G

Grant Schoep

I'm not the most avid Excel user... though I do use it for tracking alot of
personal finance stuff. One my credit card companies allows me to download
all transactions into a CSV. Works great, but they pick an ugly date
format... "20051213120000[0:GMT]"

As a programmer, I just always run this through a util I wrote that quickly
parses 20051213120000[0:GMT] in to 2005/12/13 and throws the rest away. I
was just wondering if there was a way within Excel to convert it. The date
stamp they give always follows that format. i.e. "YYYYMMDDxxxxxxxxxxxxx" I
don't care about what is in the x's.

I'm sure there is some way to do this in excel. Just not finding the
"Help" very helpful... though I probably don't know how word it properly to
find what I am looking for.
 
P

Pete_UK

Assuming the data is in column A beginning A1, insert a new column B
and enter this formula in cell B1:

=VALUE(MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

The cell should format itself as date, so copy the formula down for as
many rows as you have entries in column A (a quick way is to select B1
then double-click the fill handle - the small black square in the
bottom right corner of the cursor). Highlight column B then <copy> and
Edit | Paste Special | Values (check) and OK then <Esc>. You can then
delete column A if you don't want it for anything else.

Hope this helps.

Pete
 
R

Ron Rosenfeld

I'm not the most avid Excel user... though I do use it for tracking alot of
personal finance stuff. One my credit card companies allows me to download
all transactions into a CSV. Works great, but they pick an ugly date
format... "20051213120000[0:GMT]"

As a programmer, I just always run this through a util I wrote that quickly
parses 20051213120000[0:GMT] in to 2005/12/13 and throws the rest away. I
was just wondering if there was a way within Excel to convert it. The date
stamp they give always follows that format. i.e. "YYYYMMDDxxxxxxxxxxxxx" I
don't care about what is in the x's.

I'm sure there is some way to do this in excel. Just not finding the
"Help" very helpful... though I probably don't know how word it properly to
find what I am looking for.
Click to expand...

To convert that to your desired text string:

=TEXT(LEFT(A1,8),"0000\/00\/00")

If you want an Excel type date, which is a serial number, then precede the
above by a double unary, and format like yyyy/mm/dd (or however you wish)

=--TEXT(LEFT(A1,8),"0000\/00\/00")


--ron
 
Top