if statement - remove the data in a cell

ncapanzana

New Member
Joined
Mar 13, 2013
Messages
13
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

ABC
UK1235455anyvalue1
anyvalue1
UK8941521anyvalue24anyvalue24
GS8945122anyvalue345
LM8944551anyvalue84
UK8941122anyvalue14anyvalue14

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Excel 2010
ABC
1UK1235455anyvalue1anyvalue1
2UK8941521anyvalue24anyvalue24
3GS8945122anyvalue345
4LM8944551anyvalue84
5UK8941122anyvalue14anyvalue14

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

Worksheet Formulas
CellFormula
C1=IF(LEFT(A1,2)="UK",B1,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

ncapanzana

New Member
Joined
Mar 13, 2013
Messages
13
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

New Member
Joined
Mar 13, 2013
Messages
13

ADVERTISEMENT

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

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
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

New Member
Joined
Mar 13, 2013
Messages
13

ADVERTISEMENT

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

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Run this code in a standard module

Code:
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

New Member
Joined
Mar 13, 2013
Messages
13
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

Code:
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

New Member
Joined
Mar 13, 2013
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,535
Messages
5,596,738
Members
414,094
Latest member
dingo_baby

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