Conditionally format data in column

[Carpenter]

I have a column of data similar to the following:

A12312-01
A12445-02
W34323-01
A34298-01
W54732-01

and so on.

I am querying all of this from a database with MSQuery.

I need to take all instances that start with "W" and truncate the "01" from the end. If the value does not start with "W", the suffix "0x" must remain.


Thanks for helping a newb!

 

[Norie]

Carpenter

Why not use the following option "Fill down formulas in columns adjacent to data", which is availbale via right click and Data Range Properties?

By the way could you not do this in MS Query or the database the data is coming from?

 

[Carpenter]

Why not use the following option "Fill down formulas in columns adjacent to data", which is availbale via right click and Data Range Properties?

I didn't know about this one... I'll check it out for future reference.

By the way could you not do this in MS Query or the database the data is coming from?

I'm doing this as a little intern side project... don't have any DBA privelages... believe me, I'd have this all under a few more layers of abstraction if I could, as I am supposed to teach alot of the everyday users how to set this all up. The column that I am working with is already comprised of two separate columns via SQL in MSQuery.... I figured it would be easier to get the formatting done in excel.

 

[Norie]

I did a small test and was able to edit the SQL and use Iif in MSQuery to do what you want before the data was returned to Excel.

Iif(Left([MyField], 1)="W", Left([MyField], Len([MyField])-3), [MyField])

I was extracting data from an Access database, I don't know
if that would make a difference.

 

[Carpenter]

Excellent. I'm no expert on SQL, so it is definately great to learn that, as it will surely come in handy. I'll get it to work, then ask my boss how he wants me to do it.

Thanks guys.... i start with a problem that i cant solve, and 2 horus later i can impliment two separate methods.