Any excel experts out there?

[Stereo Steve]

I'm recording a macro to convert a text file into a csv and save it to the desktop ready for importing into another program. I'm nearly there with it but am stuck on one bit.

First, we are converting the file to csv. Column A is the order number and looks like 111-1111111-111111. I want to remove the first 12 characters
so have applied the formula =right(a1,len(a1)-12) to an empty cell on the right. Then, this is pasted back to a1 as a value only so we get the desired result. The formula column is then deleted.

Now, the problem is that the file has a varying amount of rows every time the macro is used. If I apply the formula to say, 200 rows, I get a load of cells with #VALUE in them where there there was nothing in a1.

So,is there a qualifier to apply the formula only if A1 has something in it? Or can I somehow delete any cell which has #VALUE in it in some way?

[completerookie]

try something like

=if(a1="","",(a1,len(a1)-12) )

the trick is to put an IF(test-condition,true-action,false-action) around the formula

you have to play around with the IF() to see if its easier to test for an empty cell or test if the cell has contents,

when your'e playing, use the "=" button at the top left corner of the screen to see what sort of formulas you can use and also look up the concept od "named areas"
in this instance, you create a named area of "refNum" as the whole of column "A" and then you can change the formular to say

=if(refNum="","",(refNum,len(refNum)-12) )

it makes the formulas so much easier to read.

[Stereo Steve]

Cheers, I'll have a play.

[Stereo Steve]

OK, got something working but when the box is empty, the IF function returns the word FALSE instead of not doing anything.

[Stereo Steve]

OK, sorted, I can copy the result of the formula as a value back to the A column and then find and replace all the 'FALSE' words with nothing to clear them. Maybe not the prettiest way to do it but it works.

Cool, thanks for your help.

[completerookie]

post the formula, lets have a look and then make suggestions from there

you can use things like "iserr", "iserror", "isblank", "isna", "istext" and so on.

[Stereo Steve]

OK, I'm not at work now but off the top of my head. This is in I1.

=IF(A1>1,RIGHT(A1,LEN(A1)-12))

I think anyway. This either removes the first 12 characters if there is an order number in column 1 or it returns 'FALSE' if it's empty. I then cut the column with the formula (I) and paste into A as a value so it is then a numerical value or 'FALSE' . I then do find and replace on that column to replace 'FALSE' with nothing. This clears all the falses.

It may seem like a work around but it's done using macro recorder in excel 2007 so it's only done once. After that we just run the macro and the result is exactly how we want it.

We are basically trying to turn an Amazon txt file into a csv file which we can import into City Link's CLAN software address book. That way, we only have to enter the last 6 digits of the amazon order number and CLAN fills in the rest 99% of the time. Similar to what we do with Ebay downloads.

So far so good. It works!

[leehack]

The IF function has another option, and can be read as:

IF this condition is this, THEN do this, IF NOT, THEN do this.

Therefore, you have no need to have FALSE in the column, you can automatically tell it not to put anything in there:

=IF(A1>1,RIGHT(A1,LEN(A1)-12) ,"")

Blue = Condition
Red = What to do if condition is met
Green = What to do if condition is not met

IF, THEN, ELSE basically

There is an added bonus that the ELSE part can then be the start of a whole new IF, so you can have nested IFs. You can only nest a maximum of 6 though, so you have 6 IFs being asked plus the ELSE, that gives you 7 conditions you can act upon.

Here's one of mine on a golf spreadsheet:

=IF(HL37="","",IF(SUM(HL37-ROUND(HN37,0))-$GX$4>0,"+"&SUM(HL37-ROUND(HN37,0))-$GX$4,
IF(SUM(HL37-ROUND(HN37,0))-$GX$4=0,"Even",SUM(HL37-ROUND(HN37,0))-$GX$4)))

[completerookie]

max nested 6 times is a new one on me, I've learn't something new.

I've nested if's lots of times, but can't ever remember getting close to the 6 limit.
cheers Lee

[Stereo Steve]

Ta Lee, I'll try that. The bad thing about the macro recorder is that it makes you lazy as you can make mistakes while recording, undo them and try again! The end result is the same. That will tidy it up though.

[leehack]

max nested 6 times is a new one on me, I've learn't something new.

I've nested if's lots of times, but can't ever remember getting close to the 6 limit.
cheers Lee

To be honest Kevin, they might have increased the ability now to have more, this was 10 years ago i found it out, although was still the case in excel 2003 i think. It always used to bugger up on the 7th which i only found out cos i wanted 7 conditions. This was when i found the else part that gave me the 7th.

You can actually use more than 6, its just that you need to use multiple cells which gets messy. You then have an if running on the multiple cells to find which result to use lol.

Excel used to be my fave, i kind of miss it now hardly ever using it, best product ever made IMO.

[completerookie]

wrote a complete complex wages programme in Excel 2k - ah memories, those were the days,

Now we've swapped to quickbook wages (really bad but got fed up with all those new rules and reg changes) - just comeback from trying to work around a really bad bug with the wages-slips printed direct from QB

att least with excel, I could look at the code and work out what's happening - not like QB