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!

 

[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)

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

 

[Legacy 51064]

Try:

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

 

[Carpenter]

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]

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:

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]

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]

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.