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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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
 

Carpenter

New Member
Joined
Nov 30, 2005
Messages
12

ADVERTISEMENT

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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 30, 2005
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,820
Messages
5,574,508
Members
412,599
Latest member
Schu94
Top