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>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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,484
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,484
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,088
Messages
5,835,321
Members
430,351
Latest member
ddalton

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