# Conditionally format data in column

#### Carpenter

##### New Member
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!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Joe4

I am not sure how do that with Conditional Formatting (or even if it is possible).

Via formula, for a value in cell A1, you can use:

=IF(LEFT(A3,1)="W",LEFT(A3,LEN(A3)-3),A3)

Note: This also removes the "-" too. IF you want to keep it, change "-3" to "-2".

#### Joe4

To actually physically change the value in the cell it resides in, you can use a macro, i.e. (this example on the range A1:A100)
Code:
``````Sub FixValues()

Dim cell As Range
For Each cell In Range("A1:A100")
If Left(cell, 1) = "W" Then cell = Left(cell, Len(cell) - 3)
Next cell

End Sub``````

L

#### Legacy 51064

##### Guest
Try:

=IF(LEFT(A1,1)="W",MID(A1,2,LEN(A1)-4),A1)

#### Carpenter

##### New Member
Thanks for your response... I can use that method to fix the data for a single cell. Perhaps this is simple, but could you let me know how to apply this formula for a whole column? Since this is running on a query, the amount of data in the spreadsheet is dynamic, and can range from one row to a few thousand.

#### Carpenter

##### New Member
Edit.. i just noticed the code.

I'll give that a try

#### Joe4

You can make the code more dynamic with a slight modification.

For example, let's say that the data is always in column A, but you don't know how many rows there will be. Then try this:
Code:
``````Sub FixValues()

Dim cell As Range
For Each cell In Range("A1:A" & Range("A65536").End(xlUp).Row)
If Left(cell, 1) = "W" Then cell = Left(cell, Len(cell) - 3)
Next cell

End Sub``````

#### Carpenter

##### New Member
I have the macro in now, but I am unsure where to run it. As of now, I run it through tools->macro. Is there any way to have the macro run when I refresh the data?

#### Joe4

That depends on how you are refreshing your data. You may be able to put the code in an event procedure, which will automatically trigger the macro based on some event happening.

Here is a good write up on event procedures:
http://www.cpearson.com/excel/events.htm

#### Carpenter

##### New Member
Thanks for all the help.

I wrote the query with MSQuery... after I place the correct parameter in a cell, I click "Refresh data" on the external data toolbar.

I will have to tinker with all of these events to find the most intuitive approach.

Replies
0
Views
130
Replies
1
Views
441
Replies
2
Views
169
Replies
3
Views
333
Replies
5
Views
174

1,171,047
Messages
5,873,463
Members
432,980
Latest member
KMorrison12345

### 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.

### Which adblocker are you using?

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

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