Conditionally format data in column

Carpenter

New Member
Joined
Nov 30, 2005
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Carpenter

New Member
Joined
Nov 30, 2005
Messages
12
Norie said:
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.


Norie said:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 30, 2005
Messages
12
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. :)
 

Forum statistics

Threads
1,171,794
Messages
5,877,555
Members
433,265
Latest member
Umaratnam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top