if statement - remove the data in a cell

[ncapanzana]

Hello,

I need a formula to run in B column where

if the value in column A DOES NOT START WITH 'UK', remove the value/data in column B

Expected results

A	B	C
UK1235455	anyvalue1	anyvalue1
UK8941521	anyvalue24	anyvalue24
GS8945122	anyvalue345
LM8944551	anyvalue84
UK8941122	anyvalue14	anyvalue14​

<tbody>
</tbody>

 

[ncapanzana]

expected results in Column C

 

[jim may]

Excel 2010

	A	B	C
1	UK1235455	anyvalue1	anyvalue1
2	UK8941521	anyvalue24	anyvalue24
3	GS8945122	anyvalue345
4	LM8944551	anyvalue84
5	UK8941122	anyvalue14	anyvalue14

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C1 =IF(LEFT(A1,2)="UK",B1,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[ncapanzana]

thanks.

What if I do not want to run it on column C. I want to use/run it in column B

same requirements..

 

[ncapanzana]

or shall i say I do not need to have column C...

 

[jim may]

seems to me you need the column B data before you can determine the column C Data; You could Hide Column B, showing only columns A & C...
Or With your original Column B data you could use code/script to modify it to look like the above column C

 

[ncapanzana]

Hi Jim,

Im going to re-phrase my requirements

Pls disregard column C

so if

In column A, doesn't start with "UK" - remove/delete the value in column B

Thanks
Nino

 

[jim may]

Run this code in a standard module

Sub FixColumnB()
Lr = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A2:A" & Lr)   'Assumes you have a Header row in row1 - your data starts on Row 2
For Each c In Rng
    If Left(c, 2) <> "UK" Then
        c.Offset(, 1).Value = ""
    End If
Next c
End Sub

 

[ncapanzana]

Thanks Jim.

It works.. another one, pls.

what if the column B is in column BA - can you edit this code?

Thanks
Nino

Run this code in a standard module

Sub FixColumnB()
Lr = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A2:A" & Lr)   'Assumes you have a Header row in row1 - your data starts on Row 2
For Each c In Rng
    If Left(c, 2) <> "UK" Then
        c.Offset(, 1).Value = ""
    End If
Next c
End Sub

 

[ncapanzana]

Hello,

Can anyone help me on this pls..

Jim's code is working.. there was a change in the column.. Instead of column B the data i now in column BA.. can you edit the code to reflect the new changes.

thanks
Nino

Thanks Jim.

It works.. another one, pls.

what if the column B is in column BA - can you edit this code?

Thanks
Nino